Simple Explanation of Stored Procedure with Examples

Stored Procedure

SQL Server Stored Procedure

Overview:
A stored procedure is nothing more than Pre-Prepared SQL code that you save so you can reuse the code over and over. So if you think about a query that you write over and over, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure. In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure.

Following are the main reasons for using stored procedures:

• Precompiled execution: SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
• Reduced client/server traffic: If network bandwidth is a concern in your environment, you’ll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
• Efficient reuse of code and programming abstraction: Stored procedures can be used by multiple users and client programs. If you use them in a planned way, you’ll find the development cycle takes less time.
• Enhanced security controls: You can grant users permission to execute a stored procedure.

Creating a Sample DataBase and Table with Sample data to Understand Stored Procedure Concepts:
By using the below script we are creating a Sample DataBase Named: StoredProcDemo. Then in this database we are creating Customer table and inserting five records in this table.

CREATE DATABASE StoredProcDemo
GO
USE StoredProcDemo
GO

Create Table Customer
(
CustomerID int Identity(1,1) NOT NULL,
Name Varchar(100) Not NULL,
DateOfBirth DateTime,
City Varchar(50),
State Varchar(50)
)
GO
INSERT INTO Customer
VALUES(‘Customer1′,’06/18/2000′,’Bangalore’,’Karnataka’)

INSERT INTO Customer
VALUES(‘Customer2′,’06/10/1972′,’Pune’,’Maharastra’)

INSERT INTO Customer
VALUES(‘Customer3′,’01/18/1975′,’Mysore’,’Karnataka’)

INSERT INTO Customer
VALUES(‘Customer4′,’06/06/1974′,’Chennai’,’TamilNadu’)

INSERT INTO Customer
VALUES(‘Customer5,’06/18/2001′,’Bangalore’,’Karnataka’)

GO

Creating a simple stored procedure:
In this example we are creating a Stored Procedure  GetAllCustomers, which returns all the customer details

CREATE PROCEDURE GetAllCustomers
AS
BEGIN
Select * FROM CUSTOMER
END

Now we will get query in our mind i.e. How to execute stored procedure? Following are the two different ways we can execute/call a Stored Procedure:

GetAllCustomers
OR
Exec GetAllCustomers

To check the content of an existing stored procedure we can use the statement like below:
sp_helptext  GetAllCustomers

Stored procedure with parameters:In the below example we are creating a stored procedure named GetCityCustomers which has @CityName as input parameter and this stored procedure returns all the customer in the given input city from the Customer table

CREATE PROCEDURE GetCityCustomers
@CityName varchar(50)
AS
BEGIN
SELECT *
FROM Customer
WHERE City = @CityName
END
GO

Following are the few different ways we can execute/call a Stored Procedure with Parameter:

EXEC GetCityCustomers ‘Bangalore’
–OR–
GetCityCustomers ‘Bangalore’
–OR–
GetCityCustomers @CityName = ‘Bangalore’

Default Parameter Values:Try Executing the stored procedure GetCityCustomers without parameter value:
EXEC GetCityCustomers

What is the result? Yes we will get errors because we are not passing the parameter.

Now we will get question, whether we can create a stored procedure with parameter which can take default value for the parameter if it is not passed? Yes we can do it, let us see it with example.
With below statement we can modify existing stored procedure GetCityCustomers to make the parameter @CityName as optional. Now if user is not passing the parameter value, it will take BANGALORE as default value for the parameter @cityname.

ALTER PROCEDURE GetCityCustomers
@CityName varchar(50) = ‘BANGALORE’
AS
BEGIN
SELECT *
FROM Customer
WHERE City = @CityName
END

Now try executing the previous statement EXEC GetCityCustomers . This time it works, because if we are not passing the input parameter @CityName value it value take the default value BANGALORE mentioned in the Stored Procedure definition. If we are explicitly passing the  input parameter value then it will over-ride the default value.

Now modify the stored procedure to return all the customer details if we don’t pass the input parameter @CityName by the below statement:

ALTER PROCEDURE GetCityCustomers
@CityName varchar(50) = NULL
AS
BEGIN
SELECT *
FROM Customer
WHERE (@CityName IS NULL OR City = @CityName)
END

Now try executing the GetCityCustomers stored procedure with parameter and without parameter value:
EXEC GetCityCustomers
GO
EXEC GetCityCustomers ‘Mysore’
GO
EXEC GetCityCustomers ‘Bangalore’
GO

Store Procedure with Multiple Parameters:In the below example we are creating a stored procedure named GetCustomersByCityAndState which accepts two input parameters: @CityName and @State returns all the customer details from Customer table based on these input parameters.

Create PROCEDURE GetCustomersByCityAndState
@CityName varchar(50),
@State Varchar(50)
AS
BEGIN
SELECT *
FROM Customer
WHERE State = @State AND City = @CityName
END

Following are the few different ways we can execute/call stored procedure with multiple input parameters:
EXEC GetCustomersByCityAndState ‘Bangalore’,’Karnataka’
–OR
EXEC GetCustomersByCityAndState @CityName = ‘Bangalore’,@State =’Karnataka’
–OR
EXEC GetCustomersByCityAndState @State =’Karnataka’,@CityName = ‘Bangalore’

How to Drop an Existing Stored Procedure:With below statement we are dropping the stored procedure GetCustomersByCityAndState created in the previous section
DROP PROCEDURE GetCustomersByCityAndState

Stored procedure with OutPut Parameters:Let us create a stored procedure GetNoOfCustomersByCity with input parameter @CityName and output parameter @NoofCustomers. Output parameter returns the count of the number  of customers in the customer table for the input city.

Create PROCEDURE GetNoOfCustomersByCity
@CityName varchar(50),
@NoofCustomers int OutPut
AS
BEGIN
SELECT @NoofCustomers = Count(1)
FROM Customer
WHERE City = @CityName
END
GO

Below statement shows how to execute stored procedure with output parameter and also shows how we can read the output parameter value:
Declare @cnt INT
EXEC GetNoOfCustomersByCity ‘Bangalore’,@cnt OUTPUT
PRINT @cnt

RETURN Parameter:Let us create a stored procedure GetNoOfCustomersByCityWithReturnParam with input parameter @CityName and returns the count of the number of customers in the customer table for the input city by the RETURN statement.

Create PROCEDURE GetNoOfCustomersByCityWithReturnParam
@CityName varchar(50)
AS
BEGIN
DECLARE @NoofCustomers INT

SELECT @NoofCustomers = Count(1)
FROM Customer
WHERE City = @CityName

RETURN @NoofCustomers
END
GO

Below statement shows how to execute stored procedure and get the value returned by the RETURN statement. Here the variable @cnt holds the value returned by the Stored Procedure.

Declare @cnt as int
EXEC @cnt  = GetNoOfCustomersByCityWithReturnParam ‘Bangalore’
PRINT @cnt

Stored Procedure for Data Manipulation:Let us create a Stored Procedure named AddCustomer, which inserts record in the Customer table with input values

Create PROCEDURE AddCustomer
@Name Varchar(50),
@CityName varchar(50),
@DOB DATETIME
AS
BEGIN
INSERT INTO CUSTOMER (NAME,City,DateOfBirth)
VALUES (@Name,@CityName,@DOB)
END
GO

Let us use the below statement to insert record in the Customer table
EXEC AddCustomer ‘Raj’,’Bangalore’,’10/23/1988′
GO