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’)
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:
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