Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 8 April 2015

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