Insert Update Delete Operations Using Stored Procedure in SQL Server

A Stored Procedure is the collection of logical groups of SQL Statements. Stored Procedures server an important purpose in performing a specific task. The main advantage of Stored Procedures is for increasing the performance of the database. In this article I will use a Stored Procedure and do the insert, update and delete operations using a Stored Procedure. In this article I will not use the front end, all the task I will do in the backend.

Step 1

Start the SQL Server and create a table.

  1. CREATE TABLE employee1  
  2. (  
  3.    id INTEGER PRIMARY KEY,  
  4.    first_name VARCHAR(10),  
  5.    last_name VARCHAR(10),  
  6.    salary DECIMAL(10,2),  
  7.    country VARCHAR(20),  
  8. )  

Step 2

Now we need to insert the data in the table using the insert query.

  1. INSERT INTO emp  
  2. VALUES  
  3. (  
  4.    1, 'Yaten''Sharma', 15000, 'India'  
  5. );  
  6. INSERT INTO emp  
  7. VALUES  
  8. (  
  9.    2, 'Shiva''Sharma', 12000, 'Australia'  
  10. );  
  11. INSERT INTO emp  
  12. VALUES  
  13.    (3, 'Reenna''Gupta', 18000, 'USA');  
  14. INSERT INTO emp  
  15. VALUES  
  16. (  
  17.    4, 'Priyanka''Updhayay', 16000, 'India'  
  18. );  
  19. INSERT INTO emp  
  20. VALUES  
  21. (  
  22.    5, 'Rahul''Updhayay', 17000, 'India'  
  23. );  

We can check whether the data was inserted or not using the select query.

 

  1. select * from emp;  

 


Figure 1: Select table

Step 2

After inserting the data, now we will create the Stored Procedure.

  1. create procedure InsertUpdate  
  2. (  
  3.    @id INTEGER,  
  4.    @first_name VARCHAR(10),  
  5.    @last_name VARCHAR(10),  
  6.    @salary varchar(20),  
  7.    @country VARCHAR(20),  
  8.    @StatementType nvarchar(20) = ''  
  9. )  
  10. AS  
  11. BEGIN  
  12. IF @StatementType = 'Insert'  
  13. BEGIN  
  14. insert into emp(id,first_name,last_name,salary,country) values( @id, @first_name, @last_name, @salary, @country)  
  15. END  
  16. IF @StatementType = 'Select'  
  17. BEGIN  
  18. select * from emp  
  19. END  
  20. IF @StatementType = 'Update'  
  21. BEGIN  
  22. UPDATE emp SET  
  23. First_name = @first_name, last_name = @last_name, salary = @salary,  
  24. country = @country  
  25. WHERE id = @id  
  26. END  
  27. end  

For performing the delete operation we also need to create another procedure for performing the delete operation.

Delete Stored Procedure

  1. create procedure deleted  
  2. (  
  3.    @id integer,  
  4.    @StatementType nvarchar(20) = ''  
  5. as begin IF @StatementType = 'Delete' BEGIN  
  6. DELETE FROM  
  7. emp  
  8. WHERE  
  9. id = @id END end  

Step 3

Now we will perform the insert operation using a Stored Procedure, for that we need to write the execute query.

Query for the insert using execute the Stored Procedure:

  1. exec InsertUpdateDelete @id = 6,  
  2. @first_name = 'Shobit',  
  3. @last_name = 'Pandey',  
  4. @salary = 32000,  
  5. @country = 'Canada',  
  6. @StatementType = 'Insert'  

 

Now we check whether the data was inserted.

  1. Select * from emp;  

 


Figure 2: Insert procedure

Step 4

Now we will perform the update operation, for that we write the query to update the execute query.

The following is the Query to update using execute in a Stored Procedure:

  1. exec InsertUpdateDelete @id = 3,  
  2. @first_name = 'Shobit',  
  3. @last_name = 'Pandey',  
  4. @salary = 32000,  
  5. @country = 'Canada',  
  6. @StatementType = 'Update'  

 

Now we will check that the data is updated or not.

  1. Select * from emp;  

 


Figure 3: Updated table

Step 5

Now we need to perform the delete operation using a Stored Procedure.

  1. exec deleted @id = 5,  
  2. @StatementType = 'Deleted'  

 

Now we need to check that the data is deleted.

  1. Select * from emp;  

 


Figure 4: Delete rows

Summary

This article explained how to write a Stored Procedure for insert, update and delete operations and how to execute them using parameters.

I hope this article is helpful for beginners if they want to use Stored Procedures in SQL Server and perform the insert, update and delete operations.