SQL Server Stored Procedure

Stored Procedure :

A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

SQL Server stored procedures return data in four ways:

• Output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are result sets that can be retrieved one row at a time).
• Return codes, which are always an integer value.
• A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
• A global cursor that can be referenced outside 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