Store Procedure in SQL Server with Example

What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

Example: Use this below Database

Code:

USE TestDataBase

Code: Create a table to understand stored procedure

CREATE TABLE tbl_customer
(
customerID INT PRIMARY KEY IDENTITY(100000000,1),
customerSSNId INT,
customerName VARCHAR(100),
customerAge int,
customerAddressLine1 VARCHAR(100),
customerAddressLine2 VARCHAR(100),
customerCityID VARCHAR(50),
customerStateID VARCHAR(50) 
)

Code: Insert a record inside the table

INSERT INTO tbl_customer VALUES (12345678, 'Rumman Ansari', 23, 'Kolkata', 'Rajarhat', '12', '29')

Code: See the inserted record

Select * from tbl_customer

Example 1: Simple Store Procedure

Code: Create a store procedure which will select all custermer in the above table

/* Select All Customer 
Stored Procedure Example - 1 | Normal
*/

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM tbl_customer
GO;

Code: Execute your above created stored procedure.

EXEC SelectAllCustomers;

Example 2: Stored Procedure With One Parameter

Code: Create another stored procesure which will take parameter while execution

/* Select All Customer with where clause
Stored Procedure Example - 2 | Stored Procedure With One Parameter
*/

CREATE PROCEDURE SelectAllCustomers1 @Address1 nvarchar(30)
AS
SELECT * FROM tbl_customer WHERE customerAddressLine1 = @Address1
GO;

Code: Execute the above stored procedure

EXEC SelectAllCustomers1 @Address1 = "Kolkata"

Example 3: Stored Procedure With Multiple Parameters

Code: Create a Store Procedure which will take multiple parameter

Setting up multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.

/* Select All Customer with where clause
Stored Procedure Example - 3 | Stored Procedure With Multiple Parameters
*/

CREATE PROCEDURE SelectAllCustomers3 @Address1 nvarchar(30), @Name nvarchar(20)
AS
SELECT * FROM tbl_customer WHERE customerAddressLine1 =@Address1 AND customerName = @Name
GO

Code: Execute the above store procedure

EXEC SelectAllCustomers3 @Address1 = "Kolkata", @Name = "Rumman Ansari"

Example 4: Stored Procedure With OUT Parameters

Code: Create another store procedure which will return a parameter as output

/* Insert into Customer table with data records
Stored Procedure Example - 4 | Stored Procedure With Out Parameters
*/

CREATE PROCEDURE sp_insertIntoTable
 (   
@customerSSNId INT,
@customerName VARCHAR(100),
@customerAge int,
@customerAddressLine1 VARCHAR(100),
@customerAddressLine2 VARCHAR(100),
@customerCityID VARCHAR(50),
@customerStateID VARCHAR(50),
@customerIDOut INT OUT
 )
 AS
 BEGIN

 INSERT INTO tbl_customer VALUES 
 ( 
	@customerSSNId,
	@customerName,
	@customerAge,
	@customerAddressLine1,
	@customerAddressLine2,	
	@customerCityID,
	@customerStateID
 )
	SET @customerIDOut = @@IDENTITY
END

Example 5: Stored Procedure With OUTPUT Parameters

If you declare a parameter as OUTPUT, it acts as Both Input and OUTPUT

Code: Execute the above stored procedure

-- Execute
DECLARE @customerIDOut1 INT
EXEC sp_insertIntoTable 12345678, 'Ram Ansari', 23, 'Kolkata', 'Rajarhat', '12', '29', @customerIDOut1 OUT
PRINT @customerIDOut1 

-- See table content
SELECT * FROM tbl_customer

Code: Create a stored procedure which will take a input parameter as well as give a output parameter

/* Insert into Customer table with data records
Stored Procedure Example - 5 | Stored Procedure With Output Parameters
*/ 

ALTER PROCEDURE sp_insertIntoTable1
 (   
@customerSSNId INT OUTPUT,
@customerName VARCHAR(100),
@customerAge int,
@customerAddressLine1 VARCHAR(100),
@customerAddressLine2 VARCHAR(100),
@customerCityID VARCHAR(50),
@customerStateID VARCHAR(50),
@customerIDOut INT OUT
 )
 AS
 BEGIN

 INSERT INTO tbl_customer VALUES 
 ( 
	@customerSSNId,
	@customerName,
	@customerAge,
	@customerAddressLine1,
	@customerAddressLine2,	
	@customerCityID,
	@customerStateID
 )
	SET @customerIDOut = @@IDENTITY
	SET @customerSSNId = @customerSSNId+1
END

Code: Execute the above stored procedure

-- Execute
DECLARE @customerIDOut1 INT,  @customerSSNId1 INT = 12345678
EXEC sp_insertIntoTable1 @customerSSNId1 OUTPUT, 'Ram Ansari', 23, 'Kolkata', 'Rajarhat', '12', '29', @customerIDOut1 OUT
PRINT @customerIDOut1
PRINT @customerSSNId1

 
-- See the table content
SELECT * FROM tbl_customer