Post
Ask Question

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

  1. System stored procedures
  2. User-defined stored procedures
  3. 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

  1. CREATE TABLE VehicleModels  
  2. (  
  3.    Id int primary key identity,  
  4.    Model nvarchar(40),  
  5.    Description nvarchar(80),  
  6.    TotProdctionCost money,  
  7.    ProdctionSellingPrice money  
  8. )  

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

  1. Insert into VehicleModels values('L551','L551',51000, 71000)  
  2. Insert into VehicleModels values('L550','L550',41000, 61000)  
  3. Insert into VehicleModels values('L538','L538',31000, 51000)  
  4.   
  5. Select * from VehicleModels  

 Output

 

 Customers

  1. CREATE TABLE Customers  
  2. (  
  3.    Id int primary key identity,  
  4.    FirstName  nvarchar(40),  
  5.    LastName  nvarchar(40),  
  6.    Address  nvarchar(80),  
  7.    City nvarchar(40),  
  8.    State nvarchar(40),  
  9.    Country nvarchar(40)  
  10. )   

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

  1. Insert into Customers values('Shrimant','T','ABC','Latur','Maharashtra','India')  
  2. Insert into Customers values('Arun','J','ABC','Latur','Maharashtra','India')  
  3. Insert into Customers values('Kishor','D','ABC','Latur','Karnataka','India')  
  4. Insert into Customers values('Madhav','S','ABC','Latur','Karnataka','India')  
  5. Insert into Customers values('Jitendra','W','ABC','Latur','Hydrabad','India')  
  6. Insert into Customers values('Tukaram','M','ABC','Latur','Hydrabad','India')  
  7. Insert into Customers values('Aditya','W','ABC','Latur','Gujrath','India')  
  8. Insert into Customers values('Harsha','M','ABC','Latur','Gujrath','India')  
  9.   
  10. Select * from Customers  

 Output

 

ProductionTransactions
  1. CREATE TABLE ProductionTransactions  
  2. (  
  3.    Id int primary key identity,  
  4.    CustomerId int foreign key references Customers(Id),   
  5.    VehicleModelId int foreign key references VehicleModels(Id),  
  6.    DateofBooking datetime,  
  7.    Qty int  
  8. )  

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

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

 

Create Stored Procedure

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

Syntax

  1. CREATE PROCEDURE SP_Stored_Procedure_Name  
  2. AS BEGIN  
  3.    QUERY OR SQL STATEMENT  
  4. END  
Now, we are going to select all the customer from Customer table using a stored procedure.

Example

  1. CREATE PROCEDURE SP_Customers_Select  
  2. AS  
  3. BEGIN  
  4.         SELECT * FROM Customers  ---   QUERY OR SQL STATEMENT  
  5. 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

  1. 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

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

 Example

  1. CREATE PROCEDURE SP_ProductionTransactions  
  2. AS  
  3. BEGIN  
  4. SELECT c.FirstName, c.LastName, c.State, c.Country, v.Model, p.DateofBooking, p.Qty As 'VehiclePurchaseQuantity'  FROM Customers c  
  5. INNER JOIN  
  6. ProductionTransactions p ON c.Id = p.CustomerId  
  7. INNER JOIN  
  8. VehicleModels v ON v.Id =  p.VehicleModelId  
  9. END  

 Output

Insert Stored Procedure

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

Example

  1. CREATE PROCEDURE SP_Customers_insert  
  2. (  
  3. @FirstName  nvarchar(40), —-Input Parameters  
  4. @LastName  nvarchar(40),   
  5. @Address  nvarchar(80),   
  6. @City nvarchar(40),   
  7. @State nvarchar(40),   
  8. @Country nvarchar(40))  
  9. AS   
  10. BEGIN  
  11.     INSERT INTO Customers values(@FirstName, @LastName, @Address, @City, @State, @Country); -Passing parameter 
  12. END   
Execute Stored Procedure to insert the one record in the table.
  1. Exec SP_Customers_insert 'Yash', 'Bhan','Baner','Pune','Maharashtra','India'   
Execute Stored Procedure to select all the records from the table.
  1. 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.

  1. CREATE PROCEDURE SP_Customers_Select_Specific_Record  
  2. @FirstName  nvarchar(40),   
  3. @LastName  nvarchar(40)  
  4. AS   
  5. BEGIN  
  6.     SELECT * FROM Customers WHERE FirstName = @FirstName AND LastName = @LastName;  
  7. END  

Execute Stored Procedure.

  1. 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

  1. CREATE PROCEDURE SP_Customers_Delete_Specific_Record  
  2. @Id int  
  3. AS   
  4. BEGIN  
  5.     Delete FROM Customers WHERE Id = @Id;  
  6. --Delete FROM Customers  
  7. END   
Execute Stored Procedure
  1. exec SP_Customers_Delete_Specific_Record 10  
  2.   
  3. Exec SP_Customers_Select  
Output
 
 

 Drop Stored Procedure

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

Syntax

  1. DROP PROCEDURE SP_Procedure_Name   
Example
  1. CREATE PROCEDURE SP_Customers_Delete_Specific_Record_copy  
  2. @Id int  
  3. AS   
  4. BEGIN  
  5.     Delete FROM Customers WHERE Id = @Id;  
  6. END  
  7.   
  8. DROP PROCEDURE SP_Customers_Delete_Specific_Record_copy  
If you wanted to see the stored procedure is created or not, then use following steps-

 

  1. First, open the Object Explorer.
  2. Under expand database.
  3. Click on the user database.
  4. 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.

  1. ALTER PROCEDURE SP_CustomerVehiclePurchaseQuantityStateWise  
  2. AS  
  3. BEGIN  
  4.         SELECT  c.State, v.Model, p.Qty As 'VehiclePurchaseQuantity', P.DateofBooking    
  5.         FROM Customers c  
  6.         INNER JOIN  
  7.         ProductionTransactions p ON c.Id = p.CustomerId  
  8.         INNER JOIN  
  9.         VehicleModels v ON v.Id =  p.VehicleModelId  
  10.         GROUP BY c.State, v.Model, p.Qty,  P.DateofBooking     
  11.   
  12. END  
  13.   
  14. Exec SP_CustomerVehiclePurchaseQuantityStateWise   
Output

 

View Stored Procedure Information

Example

  1. 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

  1. CREATE PROCEDURE SP_MOdelWiseCustomer  
  2. @Model nvarchar(40),  
  3. @TotNoOfCustomers int out  
  4. AS  
  5. BEGIN  
  6.         --SELECT  c.Id, c.FirstName, c.LastName, c.City, c.State, c.Country, v.Model, p.Qty As 'VehiclePurchaseQuantity'  
  7.         select @TotNoOfCustomers = Count(c.Id)   
  8.         FROM Customers c  
  9.         INNER JOIN  
  10.         ProductionTransactions p ON c.Id = p.CustomerId  
  11.         INNER JOIN  
  12.         VehicleModels v ON v.Id =  p.VehicleModelId  
  13.         where  v.Model = @Model  --'L551'     
  14. 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

  1. DECLARE @TotNoOfCustomers INT    
  2. Execute SP_MOdelWiseCustomer 'L551', @TotNoOfCustomers OUT    
  3. 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

  1. CREATE PROCEDURE SP_Customers_Select_Column_Optional     
  2.     @FirstName nvarchar(40) = NULL,  
  3.     @LastName nvarchar(40) = NULL,  
  4.     @Address nvarchar(80) = NULL,  
  5.     @City nvarchar(40) = NULL,  
  6.     @State nvarchar(40) = NULL,  
  7.     @Country nvarchar(40) = NULL  
  8. AS    
  9. BEGIN    
  10. SELECT * FROM Customers WHERE     
  11. (FirstName = @FirstName or @FirstName IS NULL) AND (LastName = @LastName OR @LastName IS NULL) AND  
  12.  (Address = @Address OR @Address  IS NULL) AND (City = @City OR @City  IS NULL) AND  
  13.    (State = @State OR @State IS NULL) AND (Country = @Country OR @Country IS NULL)   
  14. 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

  1. EXEC SP_Customers_Select_Column_Optional    
Output

 

Passing values to the stored procedure
  1. 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

  1. CREATE PROCEDURE  SP_Customer_CURD_Operation  
  2. (   
  3.     @Action Varchar (10),  
  4.     @Id int=NULL,  
  5.     @FirstName nvarchar(40) = NULL,  
  6.     @LastName nvarchar(40) = NULL,  
  7.     @Address nvarchar(80) = NULL,  
  8.     @City nvarchar(40) = NULL,  
  9.     @State nvarchar(40) = NULL,  
  10.     @Country nvarchar(40) = NULL)  
  11. AS  
  12. BEGIN  
  13.     SET NOCOUNT ON;  
  14.   
  15.     IF @Action='INSERT' -- To insert the record     
  16.         BEGIN  
  17.             INSERT INTO Customers(FirstName, LastName, Address, City, State, Country)values(@FirstName, @LastName, @Address, @City, @State, @Country)  
  18.         END    
  19.   
  20.     ELSE IF @Action='SELECT'   -- To select the record     
  21.         BEGIN  
  22.             SELECT * FROM Customers  
  23.         END  
  24.   
  25.     ELSE IF @Action='UPDATE'  -- To update the record     
  26.         BEGIN  
  27.             UPDATE Customers set FirstName = @FirstName, LastName = @LastName, Address = @Address, City = @City, State= @State, Country = @Country  where Id=@Id  
  28.         END  
  29.      ELSE IF @Action='DELETE'  -- To delete the record     
  30.         BEGIN  
  31.             DELETE FROM Customers WHERE Id=@Id  
  32.         END  
  33.  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.
  1. 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. 
  1. 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. 
  1. 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.
  1. 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

  1. ALTER PROCEDURE [dbo].[SP_Customers_Select]  
  2. AS  
  3. BEGIN  
  4.         SET NOCOUNT ON  
  5.         SELECT * FROM Customers  ---   QUERY OR SQL STATEMENT  
  6. END  
  7. GO  
Output

 

SET NOCOUNT OFF Keyword

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

Example

  1. ALTER PROCEDURE [dbo].[SP_Customers_Select]  
  2. AS  
  3. BEGIN  
  4.         SET NOCOUNT OFF  
  5.         SELECT * FROM Customers  ---   QUERY OR SQL STATEMENT  
  6. END  
  7. GO  
Output
 

Conclusion

I hope you understand the concept, please post your feedback, question, or comments about this blog to improve the content quality.
Next Recommended Reading Efficient Paging Using Stored Procedure In SQL Server