Creating a Stored Procedure From http://www.codeproject.com/Articles/126898/Sql-Server-How-To-Write-a-Stored-Procedure-in-SQL
DROP PROCEDURE <ProcedureName>
e.g.
DROP PROCEDURE Getstudentname
Declare @Studentname as nvarchar(200) -- Declaring the variable to collect the Studentname
Declare @Studentemail as nvarchar(50) -- Declaring the variable to collect the Studentemail
Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output
select @Studentname,@Studentemail -- "Select" Statement is used to show the output from Procedure
Execute Getstudentname 1
Exec Getstudentname 1
/*
Case when a stored procedure doesn't returns anything
*/
/*
This Stored procedure is used to Insert value into the table tbl_students.
*/
Create Procedure InsertStudentrecord
(
@StudentFirstName Varchar(200),
@StudentLastName Varchar(200),
@StudentEmail Varchar(50)
)
As
Begin
Insert into tbl_Students (Firstname, lastname, Email)
Values(@StudentFirstName, @StudentLastName,@StudentEmail)
End
/*
Stored Procedure GetstudentnameInOutputVariable is modified to collect the
email address of the student with the help of the Alter Keyword
*/
Alter PROCEDURE GetstudentnameInOutputVariable
(
@studentid INT, --Input parameter , Studentid of the student
@studentname VARCHAR (200) OUT, -- Output parameter to collect the student name
@StudentEmail VARCHAR (200)OUT -- Output Parameter to collect the student email
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname,
@StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
END
/*
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 tbl_Students WHERE studentid=@studentid
END
/* 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 tbl_Students WHERE studentid=@studentid
END