Overview Of Stored Procedure In SQL server

Introduction

In this blog, we will discuss how to work with Stored Procedure and explain the concept with an example in a simple way. I hope this is very useful for beginners and intermediate to help them understand the basic concept.

What is a Stored Procedure?

A stored procedure is a collection of compiled group of Transact-SQL statements compile only once reuse every time to perform a specific task, User just need to call it, Code block will execute sequentially and called from either a remote program, another stored procedure, or the command line, We can call it any number of times in your program.

Basically for CURD operation or reporting purpose or if there is any situation, where you need to execute again group of SQL statements and again then you can create stored procedure since they are very easy to maintain.

Advantages of Stored Procedure

Faster Execution

The Stored procedures are parsed and optimized as soon as they are created and the stored procedure is stored in memory. This means that it will execute a lot faster than sending many lines of SQL code from your application to the SQL Server.

Reduce network traffic

If you send many lines of SQL code over the network to your SQL Server, this will impact on network performance. The stored procedure reduces the network traffic and increases the performance of an application.

Change procedure code without affecting clients

We can encapsulate logic in the stored procedure in a sequence of statements and also change the procedure code without affecting clients.

Multiple clients can use the stored procedure

A stored procedure will accept input parameters so that a single procedure can be used over the network by several clients. If we modify a stored procedure all the clients will get the updated stored procedure.

Maintainable

When programming logic is centralized into stored procedures it makes changes much easier. We need to make changes in the stored procedure only.

Stronger security

Stored Procedures can be encrypted and that also prevents SQL Injection Attacks.

We have different types of stored procedures

System stored procedures
User-defined stored procedures
Extended Stored Procedures

User-defined stored procedures

User-defined stored procedures are created by the user and usually stored in a user database and are typically designed to complete the tasks.

Let's see each concept with an example in detail, for this we should have to create below tables.

How to create a Stored Procedure?

For example, suppose in the SQL server database, we have below three tables.

VehicleModels

CREATE TABLE VehicleModels
(
Id int primary key identity,
Model nvarchar(40),
Description nvarchar(80),
TotProdctionCost money,
ProdctionSellingPrice money
)

Now, you can insert the records by executing following code.

Insert into VehicleModels values('L551','L551',51000, 71000)
Insert into VehicleModels values('L550','L550',41000, 61000)
Insert into VehicleModels values('L538','L538',31000, 51000)
Select * from VehicleModels

Output

Customers

CREATE TABLE Customers
(
Id int primary key identity,
FirstName nvarchar(40),
LastName nvarchar(40),
Address nvarchar(80),
City nvarchar(40),
State nvarchar(40),
Country nvarchar(40)
)

Now, you can insert the records by executing following code.

Insert into Customers values('Shrimant','T','ABC','Latur','Maharashtra','India')
Insert into Customers values('Arun','J','ABC','Latur','Maharashtra','India')
Insert into Customers values('Kishor','D','ABC','Latur','Karnataka','India')
Insert into Customers values('Madhav','S','ABC','Latur','Karnataka','India')
Insert into Customers values('Jitendra','W','ABC','Latur','Hydrabad','India')
Insert into Customers values('Tukaram','M','ABC','Latur','Hydrabad','India')
Insert into Customers values('Aditya','W','ABC','Latur','Gujrath','India')
Insert into Customers values('Harsha','M','ABC','Latur','Gujrath','India')
Select * from Customers

Output

ProductionTransactions

CREATE TABLE ProductionTransactions
(
Id int primary key identity,
CustomerId int foreign key references Customers(Id),
VehicleModelId int foreign key references VehicleModels(Id),
DateofBooking datetime,
Qty int
)

Now, you can insert the records by executing following code.

Insert into ProductionTransactions values(1,1, DATEADD(month, -2, GETDATE()),2)
Insert into ProductionTransactions values(2,2, DATEADD(month, -2, GETDATE()),3)
Insert into ProductionTransactions values(3,3, DATEADD(month, -1, GETDATE()),1)
Insert into ProductionTransactions values(4,1, DATEADD(month, -1, GETDATE()),2)
Insert into ProductionTransactions values(5,2, DATEADD(month, -1, GETDATE()),1)
Insert into ProductionTransactions values(6,3, DATEADD(month, -1, GETDATE()),2)
Insert into ProductionTransactions values(7,1, DATEADD(month, -2, GETDATE()),2)
Insert into ProductionTransactions values(8,1, DATEADD(month, -2, GETDATE()),2)
select * from ProductionTransactions

Output

Create Stored Procedure

We need to use the following syntax to create the stored procedure.

Syntax

CREATE PROCEDURE SP_Stored_Procedure_Name
AS BEGIN
QUERY OR SQL STATEMENT
END

Now, we are going to select all the customer from Customer table using a stored procedure.

Example

CREATE PROCEDURE SP_Customers_Select
AS
BEGIN
SELECT * FROM Customers --- QUERY OR SQL STATEMENT
END

Execute Stored Procedure

We can execute the Stored Procedure using command prompt or remote program application like MVC, ASP.NET etc. We will check using the command prompt.

Example

EXEC SP_Customers_Select

Output

Select Stored Procedure with Inner join

Now, we can add the inner join query inside begin and end block of the stored procedure.

Syntax

CREATE PROCEDURE SP_Stored_Procedure_Name
AS BEGIN
SELECT Column1, Column2, Column...N
FROM Table1 t1
INNER JOIN
Table2 t2
ON t1.Id = t2.Id--(Comman column from both the table)
END

Example

CREATE PROCEDURE SP_ProductionTransactions
AS
BEGIN
SELECT c.FirstName, c.LastName, c.State, c.Country, v.Model, p.DateofBooking, p.Qty As 'VehiclePurchaseQuantity' FROM Customers c
INNER JOIN
ProductionTransactions p ON c.Id = p.CustomerId
INNER JOIN
VehicleModels v ON v.Id = p.VehicleModelId
END

Output

Insert Stored Procedure

Let’s create the stored procedure to insert the customer details in the table.

Example

CREATE PROCEDURE SP_Customers_insert
(
@FirstName nvarchar(40), —-Input Parameters
@LastName nvarchar(40),
@Address nvarchar(80),
@City nvarchar(40),
@State nvarchar(40),
@Country nvarchar(40))
AS
BEGIN
INSERT INTO Customers values(@FirstName, @LastName, @Address, @City, @State, @Country); -Passing parameter
END

Execute Stored Procedure to insert the one record in the table.

Exec SP_Customers_insert 'Yash', 'Bhan','Baner','Pune','Maharashtra','India'

Execute Stored Procedure to select all the records from the table.

Exec SP_Customers_Select

Output

Stored Procedure with where clause or stored procedure with parameters

‘@’ symbol is prefixed with the name of the parameters and variables.

Example

Stored Procedure with where clause to find or filter specific record in the table.

CREATE PROCEDURE SP_Customers_Select_Specific_Record
@FirstName nvarchar(40),
@LastName nvarchar(40)
AS
BEGIN
SELECT * FROM Customers WHERE FirstName = @FirstName AND LastName = @LastName;
END

Execute Stored Procedure.

Exec SP_Customers_Select_Specific_Record 'Yash', 'Bhan'

Output

Delete Stored Procedure specific record

Delete stored procedure is used to delete a specific or all records from the table.

Example

CREATE PROCEDURE SP_Customers_Delete_Specific_Record
@Id int
AS
BEGIN
Delete FROM Customers WHERE Id = @Id;
--Delete FROM Customers
END

Execute Stored Procedure

exec SP_Customers_Delete_Specific_Record 10
Exec SP_Customers_Select

Output

Drop Stored Procedure

If you wanted to delete any procedure then you can use below command.

Syntax

DROP PROCEDURE SP_Procedure_Name

Example

CREATE PROCEDURE SP_Customers_Delete_Specific_Record_copy
@Id int
AS
BEGIN
Delete FROM Customers WHERE Id = @Id;
END
DROP PROCEDURE SP_Customers_Delete_Specific_Record_copy

If you wanted to see the stored procedure is created or not, then use following steps-

First, open the Object Explorer.
Under expand database.
Click on the user database.
Expand Programmability -> Stored Procedure

Output

Modify Stored Procedure

To modify the stored procedure, we have to use ALTER Procedure Stored_Procedure_Name.

Example

We are going to display the state wise customer and bought vehicle quantity.

ALTER PROCEDURE SP_CustomerVehiclePurchaseQuantityStateWise
AS
BEGIN
SELECT c.State, v.Model, p.Qty As 'VehiclePurchaseQuantity', P.DateofBooking
FROM Customers c
INNER JOIN
ProductionTransactions p ON c.Id = p.CustomerId
INNER JOIN
VehicleModels v ON v.Id = p.VehicleModelId
GROUP BY c.State, v.Model, p.Qty, P.DateofBooking
END
Exec SP_CustomerVehiclePurchaseQuantityStateWise

Output

View Stored Procedure Information

Example

exec sp_helptext 'SP_CustomerVehiclePurchaseQuantityStateWise';

Output

Stored Procedure with an output parameter

We use out or output keyword to create a stored procedure with an output parameter.

Let’s create a stored procedure with output parameter, we look at how many customers bought the ‘L551’ vehicle model.

Example

CREATE PROCEDURE SP_MOdelWiseCustomer
@Model nvarchar(40),
@TotNoOfCustomers int out
AS
BEGIN
--SELECT c.Id, c.FirstName, c.LastName, c.City, c.State, c.Country, v.Model, p.Qty As 'VehiclePurchaseQuantity'
select @TotNoOfCustomers = Count(c.Id)
FROM Customers c
INNER JOIN
ProductionTransactions p ON c.Id = p.CustomerId
INNER JOIN
VehicleModels v ON v.Id = p.VehicleModelId
where v.Model = @Model --'L551'
END

Note : Let’s look at the stored procedure, As it seems there are two parameter , One is input parameter @Model and other output parameter declared without keyword @TotNoOfCustomers.

To get the result in the output parameter

DECLARE @TotNoOfCustomers INT
Execute SP_MOdelWiseCustomer 'L551', @TotNoOfCustomers OUT
SELECT @TotNoOfCustomers

In SQL to declare the variable we use DECLARE keyword.

Output

Four customers bought the ‘L551’ vehicle model.

Stored Procedure with optional Parameter

We have discussed how to create Stored Procedures with and without input parameters. We have also seen how to create a Stored Procedure with output parameters.

Now we are going to learn how to create Stored Procedure using optional parameters. We specify a default value to the parameters to make it optional in SQL server.

Example

CREATE PROCEDURE SP_Customers_Select_Column_Optional
@FirstName nvarchar(40) = NULL,
@LastName nvarchar(40) = NULL,
@Address nvarchar(80) = NULL,
@City nvarchar(40) = NULL,
@State nvarchar(40) = NULL,
@Country nvarchar(40) = NULL
AS
BEGIN
SELECT * FROM Customers WHERE
(FirstName = @FirstName or @FirstName IS NULL) AND (LastName = @LastName OR @LastName IS NULL) AND
(Address = @Address OR @Address IS NULL) AND (City = @City OR @City IS NULL) AND
(State = @State OR @State IS NULL) AND (Country = @Country OR @Country IS NULL)
END

Note: In the above-stored procedure there is six input parameter, to make it optional above we declared optional values as NULL.

Without passing any values to the stored procedure

EXEC SP_Customers_Select_Column_Optional

Output

Passing values to the stored procedure

Exec SP_Customers_Select_Column_Optional @FirstName = 'Kishor'

When we execute the above query, the SQL server overrides the existing default ‘Null’ value. KIshor D’s record will display.

Output

Single Stored Procedure for CURD Operation

CREATE PROCEDURE SP_Customer_CURD_Operation
(
@Action Varchar (10),
@Id int=NULL,
@FirstName nvarchar(40) = NULL,
@LastName nvarchar(40) = NULL,
@Address nvarchar(80) = NULL,
@City nvarchar(40) = NULL,
@State nvarchar(40) = NULL,
@Country nvarchar(40) = NULL)
AS
BEGIN
SET NOCOUNT ON;
IF @Action='INSERT' -- To insert the record
BEGIN
INSERT INTO Customers(FirstName, LastName, Address, City, State, Country)values(@FirstName, @LastName, @Address, @City, @State, @Country)
END
ELSE IF @Action='SELECT' -- To select the record
BEGIN
SELECT * FROM Customers
END
ELSE IF @Action='UPDATE' -- To update the record
BEGIN
UPDATE Customers set FirstName = @FirstName, LastName = @LastName, Address = @Address, City = @City, State= @State, Country = @Country where Id=@Id
END
ELSE IF @Action='DELETE' -- To delete the record
BEGIN
DELETE FROM Customers WHERE Id=@Id
END
END

In the above-mentioned stored procedure, We have added one additional action parameter for operation purpose like INSERT, UPDATE, DELETE, SELECT. Whatever the curd operation you are doing add action parameter with particular operation verb, so the respective statement block will execute.

Selecting Records from the table

Let's add SELECT SQL verb in @Action parameter to execute the selected block.

Exec SP_Customer_CURD_Operation @Action='Select'

Inserting the Records

If you wanted to insert the record then add INSERT SQL verb in @Action parameter to execute insert block.

Exec SP_Customer_CURD_Operation @Action='Insert' , @FirstName = 'Jitendra', @LastName = 'Waghale', @Address = 'Latur', @City = 'Latur', @State ='Maharashtra', @Country = 'India'

Output

Updating Records of table

If you wanted to update the record then use UPDATE SQL verb in @Action parameter to execute update block.

Exec SP_Customer_CURD_Operation @Action='Update' ,@FirstName = 'Jit', @LastName = 'W', @Address = 'Ltr', @City = 'LTR', @State ='Maha', @Country = 'India',@id=11

Deleting the Records from table

If you wanted to delete the record then use DELETE SQL verb in @Action paramter to execute delete the block.

Exec SP_Customer_CURD_Operation @Action='delete' ,@id=11

SET NOCOUNT ON Keyword

After executing the query, it will show “Command completed successfully” in Messages tab.

Example

ALTER PROCEDURE [dbo].[SP_Customers_Select]
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM Customers --- QUERY OR SQL STATEMENT
END
GO

Output

SET NOCOUNT OFF Keyword

After executing the query, it will show how many records affected in the Messages tab.

Example

ALTER PROCEDURE [dbo].[SP_Customers_Select]
AS
BEGIN
SET NOCOUNT OFF
SELECT * FROM Customers --- QUERY OR SQL STATEMENT
END
GO

Output

Conclusion

I hope you understand the concept, please post your feedback, question, or comments about this blog to improve the content quality.

10 Comments

Join the conversation! Your thoughts help the community grow.

Manoj Kore7y ago
Excellent Article Jitendra Sir,Thanks for sharing
Nikunj Satasiya7y ago
Create and Execute Dynamic Stored Procedure and TO Create and Execute Parameterized Stored Procedure From Another Stored Procedure you should follow this link : https://coding-vila.blogspot.com/2018/11/sql-server-create-and-execute-dynamic-stored-procedure.html And https://coding-vila.blogspot.com/2018/11/sql-server-create-and-execute-parameterized-stored-procedure-from-another-stored-procedure.html
Hadshana Kamalanathan8y ago
Excellent Article. Thanks for sharing
Pravas Ranjan8y ago
Excellent Article Jitendra Sir, all things covered in superb manner and at last thanks for sharing.
Mahesh Chand8y ago
This is great. Any Blog that is more than 2 pages roughly, please publish them as an Article. Articles are promoted better. Blogs are for small tips and shorter articles. Cheers!
- Jitendra Waghale8y ago
  Thanks you sir, I will make the articals If the size of pages more then 2 else i'll write the blog.
Hiten Pandya8y ago
Good One. I think you covered majority information regarding stored procedure. Thanks for sharing :)
Farhan Ahmed8y ago
Nice. Write some article on user define function in SQL server
- Jitendra Waghale8y ago
  Hey Farhan, Hope this is helpful. https://www.c-sharpcorner.com/article/an-overview-of-user-defined-functions-in-sql-server/
- Jitendra Waghale8y ago
  I will make article on user define function as well!!!