Kuala Lumpur, , Malaysia
0 Comment | 5 Views | 24 Mar 17  | Mounic Madiraju
Writing a Stored Procedures in SQL Server
Suppose there is a table called stu_Students whose structure is given below:

CREATE TABLE stu_Students

(
[Studentid] [int] IDENTITY(1,1) NOT NULL,
[Firstname] [nvarchar](200) NOT NULL,
[Lastname] [nvarchar](200) NULL,
[Email] [nvarchar](100) NULL
)
for example, we insert the following data into the above table:

Insert into stu_Students (Firstname, lastname, Email)
Values('Anand', 'Vihari', 'anand@abc.com')

Insert into stu_Students (Firstname, lastname, Email)
Values('Prathik', 'Kumar', 'prathik@abc.com')

Insert into stu_Students (Firstname, lastname, Email)
Values('Kiran', 'Varma', 'kiran@abc.com')

Insert into stu_Students (Firstname, lastname, Email)
Values('Mounic', 'Raju', 'mounic@abc.comm')

Insert into stu_Students (Firstname, lastname, Email)
Values('Vishnu', 'Nanda', 'vishnu@abc.com')
Now, while writing a Stored Procedure, the first step will be to write the Create Procedure statement as the first statement:

Create Procedure Procedure-name
(
Input parameters ,
Output Parameters (If required)
)
As
Begin
Sql statement used in the stored procedure
End
Now, suppose we need to create a Stored Procedure which will return a student name whose studentid is given as the input parameter to the stored procedure. Then, the Stored Procedure will be:

/* Getstudentname is the name of the stored procedure*/

Create PROCEDURE Getstudentname(

@studentid INT --Input parameter , Studentid of the student

)
AS
BEGIN
SELECT Firstname+' '+Lastname FROM stu_Students WHERE studentid=@studentid
END
We can also collect the student name in the output parameter of the Stored Procedure. For example:

/*
GetstudentnameInOutputVariable is the name of the stored procedure which
uses output variable @Studentname to collect the student name returns by the
stored procedure
*/

Create PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT, --Input parameter , Studentid of the student
@studentname VARCHAR(200) OUT -- Out parameter declared with the help of OUT keyword
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname FROM stu_Students WHERE studentid=@studentid
END

    • Social Share
      for this Blog

Porfolio And Blogs
Mounic Madiraju follows various companies, these companies and employers can view Mounic's resume.
Upload your resume to get best job opportunities.

Register free!