SQL Stored Procedures

What is stored procedure in SQL?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

 /*Stored Procedure Syntax*/
 CREATE PROCEDURE procedure_name
 AS
 sql_statement
 GO;

 /*Execute a Stored Procedure*/
 EXEC procedure_name;

Stored Procedure Example

 /*Stored Procedure Example*/
 CREATE PROCEDURE student_pro
 AS
 SELECT * FROM student
 GO;

Execute the stored procedure above as follows:

 /*Execute a Stored Procedure*/
 EXEC student_pro;

Stored Procedure With One Parameter

 /*Stored Procedure Example*/
 CREATE PROCEDURE student_pro @City nvarchar(30)
 AS
 SELECT * FROM student WHERE City = @City
 GO

Execute the stored procedure above as follows:

 /*Execute a Stored Procedure*/
 EXEC student_pro @City = 'Mumbai';

Stored Procedure With Multiple Parameters

 /*Stored Procedure Example*/
 CREATE PROCEDURE student_pro @City nvarchar(30), @PostalCode nvarchar(10)
 AS
 SELECT * FROM student WHERE City = @City AND PostalCode = @PostalCode
 GO

Execute the stored procedure above as follows:

 /*Execute a Stored Procedure*/
 EXEC SelectAllCustomers @City = 'Mumbai', @PostalCode = '400023';