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.