write a Stored Procedure in SQL Server

you will learn how to write stored procedure in sql server program .. it's very simple :

Suppose there is a table called tbl_Students whose structure is given below:

CREATE TABLE  tbl_Students

(
    [Studentid] [int] IDENTITY(1,1) NOT NULL,
    [Firstname] [nvarchar](200) NOT  NULL,
    [Lastname] [nvarchar](200)  NULL,
    [Email] [nvarchar](100)  NULL
)

Support we insert the following data into the above table:

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Vivek', 'Johari', 'vivek@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Pankaj', 'Kumar', 'pankaj@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Amit', 'Singh', 'amit@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Manish', 'Kumar', 'manish@abc.comm')

Insert into tbl_Students (Firstname, lastname, Email)
 Values('Abhishek', 'Singh', 'abhishek@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 returns a 
student name whose studentid is given as the input parameter to the 
stored procedure. Then the Stored Procedure will be:

Create  PROCEDURE Getstudentname

(

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

)
AS
BEGIN
SELECT Firstname+' '+Lastname FROM tbl_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 tbl_Students 

WHERE studentid=@studentid
END

Note:-/* */ is used to write comments in one or multiple lines
-- is used to write comment in a single line
How to Alter a Stored Procedure in a Sql server:-
In SQL Server, a stored procedure can be modified with the help of the Alter keyword.Now if, we want to get student email address through the same procedure GetstudentnameInOutputVariable . So we need to modified it by adding one more output parameter " @StudentEmail " which is shown below:

/* 
Stored Procedure GetstudentnameInOutputVariable is modified to collect the
email address of the student with the help of the Alert 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

Note: It is not necessary that a stored procedure will 
have to written. It can be the case when a stored procedure doesn't 
written any thing. For Example, a stored procedure can be used to 
Insert, delete or update a sql statement. For Example the below stored 
procedure is used tp insert value into the table tbl_students.

/*
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

Execution of the Stored Procedure in SQL Server

Execution of the Stored Procedure which don't have a Output Parameter

A stored procedure is used in the Sql server with the help of the "Execute" or "Exec" Keyword. For Example, If we want to execute the stored procedure "Getstudentname", then we will use the following statement.

Execute Getstudentname 1
Exec Getstudentname 1    .