Store Procedure In SQL Server

A Stored procedure is a group of one or more pre-compiled SQL statements, the pre-compiled statements can be reused over and over again. Stored procedure can have input or output or both parameters, so the stored procedure can act based on the parameter values that are passed.

Create Store Procedure Syntax,

CREATE PROCEDURE Procedure_Name
AS
  -------------
  -------------
  sql statements
GO

Execute Procedure Syntax

EXEC Procedure_Name

Demo Database

TableName: StudentDetails

Stored Procedure Example

The following statement to create store procedure name "GetAllStudents", that returns all the students in the table

CREATE PROCEDURE GetAllStudents
AS
BEGIN
 SELCET * FROM StudentDetails
END
GO;

Execute the above created Stored procedure

EXEC GetAllStudents

Stored Procedure with single input parameter

The following statement is to create a stored procedure name "GetAllStudentsByCity", that returns particular students in the table based on the city value.

CREATE PROCEDURE GetAllStudentsByCity
@CITY VARCHAR(30)
AS
BEGIN
 SELCET * FROM StudentDetails WHERE city=@CITY
END
GO;

Execute the above stored procedure by passing city value

EXEC GetAllStudentsByCity 'London'

 The above store procedure returns all the students who belong to London city.

Stored Procedure with Multiple input parameter

The following statement is to create a stored procedure name "GetAllStudentsByCityANDZipCode" that returns particular students in the table based on the city value.

CREATE PROCEDURE GetAllStudentsByCityANDZipCode
(@CITY VARCHAR(30),@zipcode bigint)
AS
BEGIN
 SELCET * FROM StudentDetails WHERE city=@CITY AND ZipCode=@zipcode
END
GO;

Execute the above stored procedure by passing city and zip code values.

EXEC GetAllStudentsByCityANDZipCode 'Mexico',0521

 The above store procedure return all the students belongs to London city and zip code is 0521

Stored Procedure Example with output parameter

The following statement is to create store procedure name "GetZipCodeByRollNumber", which returns zipcode by student roll number.

CREATE PROCEDURE GetZipCodeByRollNumber
(@RollNumber VARCHAR(30),@zipcode BIGINT OUTPUT)
AS
BEGIN
 SELCET @zipcode=ZipCode FROM StudentDetails WHERE RollNumber=@RollNumber
END
GO;

Execute the above store procedure by passing RollNumber value

DECLARE @ZIPCODE BIGINT
EXEC GetZipCodeByRollNumber 0555888,@ZIPCODE OUTPUT

The above store procedure returns the ZIPCode value of student RollNumber 0555888 and stores it into @ZIPCODE variable

We have shown how stored procedure creates and executes with input and output parameters.