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,

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

Execute Procedure Syntax

SQL
EXEC Procedure_Name

Demo Database

TableName: StudentDetails

ID Name RollNumber Address City ZipCode
1 Kiran 0555888 120,Hanover sq London 0520
2 Balu 0445254 Matederos 2312 Mexico 0521
3 Ganesh 0652452 Matederos 2312 Mexico 0521
4 Hari 0452345 25,Hanover sq London 0520
5 Naveen 0546245 25,Hanover sq London 0520

Stored Procedure Example

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

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

Execute the above created Stored procedure

SQL
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.

SQL
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

SQL
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.

SQL
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.

SQL
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.

SQL
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

SQL
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.