Stored Procedures

---

Stored Procedures in SQL Server

A stored procedure is a type of code in SQL that can be stored for later use and can be used many times. So, whenever we need to execute the query, instead of calling it you can just call the stored procedure. Values can be passed through stored procedures.

These are of two types:

* Stored procedure with One parameter

* Stored procedure with Multiple parameters

Syntax

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute a Stored Procedure

EXEC procedure_name;

Now,let us consider a customers table:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Rajivlal	Raja	Ramnagar 124	Hyderabad	05021	India
2	Banveerji	Bannu	Rajivnagar 114	Ahmednagar	WA1 1DP	India

Stored Procedure Example

The following SQL statement creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table:

Example

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Execute the stored procedure above as follows:

Example

EXEC SelectAllCustomers;

1.Stored Procedure With One Parameter

The following SQL statement creates a stored procedure that selects Customers from a particular City from the "Customers" table:

Example

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

Execute the stored procedure above as follows:

Example

EXEC SelectAllCustomers City = "Ahmednagar";

2.Stored Procedure With Multiple Parameters

Setting up multiple parameters is very easy.The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Customers" table:

Example

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

Execute the stored procedure above as follows:

Example

EXEC SelectAllCustomers City = "Ahmednagar", PostalCode = "WA1 1DP";