Select, Insert, Update, Delete Using Stored Procedure in SQL Server 2008

Introduction

 
Here, we will see how to create select, insert, update, delete statements using stored procedures in SQL Server. Let's take a look at a practical example. We create a table.
 
Creating Table 
  1. CREATE TABLE employee  
  2.   (  
  3.      id         INTEGER NOT NULL PRIMARY KEY,  
  4.      first_name VARCHAR(10),  
  5.      last_name  VARCHAR(10),  
  6.      salary     DECIMAL(10, 2),  
  7.      city       VARCHAR(20),  
  8.   )   
Now add some rows to the table. We can add new rows using an INSERT INTO SQL statement. Then execute a SELECT SQL query to display all records of the table.
  1. INSERT INTO employee  
  2. VALUES      (2,  
  3.              'Monu',  
  4.              'Rathor',  
  5.              4789,  
  6.              'Agra');  
  7.   
  8. go  
  9.   
  10. INSERT INTO employee  
  11. VALUES      (4,  
  12.              'Rahul',  
  13.              'Saxena',  
  14.              5567,  
  15.              'London');  
  16.   
  17. go  
  18.   
  19. INSERT INTO employee  
  20. VALUES      (5,  
  21.              'prabhat',  
  22.              'kumar',  
  23.              4467,  
  24.              'Bombay');  
  25.   
  26. go  
  27.   
  28. INSERT INTO employee  
  29. VALUES      (6,  
  30.              'ramu',  
  31.              'kksingh',  
  32.              3456,  
  33.              'jk');  
  34.   
  35. go  
  36.   
  37. SELECT *  
  38. FROM   employee   
The table looks like this.
 
employeetable.gif
Figure 1
 
Stored Procedure for Select, Insert, Update, Delete
 
Here, we create a stored procedure with SELECT, INSERT, UPDATE, and DELETE SQL statements. The SELECT SQL statement is used to fetch rows from a database table. The INSERT statement is used to add new rows to a table. The UPDATE statement is used to edit and update the values of an existing record. The DELETE statement is used to delete records from a database table. The following SQL stored procedure is used insert, update, delete, and select rows from a table, depending on the statement type parameter. 
  1. ALTER PROCEDURE Masterinsertupdatedelete (@id            INTEGER,  
  2.                                           @first_name    VARCHAR(10),  
  3.                                           @last_name     VARCHAR(10),  
  4.                                           @salary        DECIMAL(10, 2),  
  5.                                           @city          VARCHAR(20),  
  6.                                           @StatementType NVARCHAR(20) = '')  
  7. AS  
  8.   BEGIN  
  9.       IF @StatementType = 'Insert'  
  10.         BEGIN  
  11.             INSERT INTO employee  
  12.                         (id,  
  13.                          first_name,  
  14.                          last_name,  
  15.                          salary,  
  16.                          city)  
  17.             VALUES     ( @id,  
  18.                          @first_name,  
  19.                          @last_name,  
  20.                          @salary,  
  21.                          @city)  
  22.         END  
  23.   
  24.       IF @StatementType = 'Select'  
  25.         BEGIN  
  26.             SELECT *  
  27.             FROM   employee  
  28.         END  
  29.   
  30.       IF @StatementType = 'Update'  
  31.         BEGIN  
  32.             UPDATE employee  
  33.             SET    first_name = @first_name,  
  34.                    last_name = @last_name,  
  35.                    salary = @salary,  
  36.                    city = @city  
  37.             WHERE  id = @id  
  38.         END  
  39.       ELSE IF @StatementType = 'Delete'  
  40.         BEGIN  
  41.             DELETE FROM employee  
  42.             WHERE  id = @id  
  43.         END  
  44.   END   
Now press F5 to execute the stored procedure. This will create a new stored procedure in the database.
 
Now open object explorer and select storeprocedure MasterInsertUpdateDelete.
 
Stored Procedure to Check Insert
 
StatementType = 'Insert'
 
MasterInsertUpdateDelete -> right click select Execute Stored Procedure...
 
employeetable1.gif
Figure 2
 
Execute procedure window will be opened.
 
employeetable3.gif
Figure 3
 
Now for insert, we fill the data in values in the required fields.
 
StatementType=insert
 
employeetable4.gif
Figure 4
 
Click on the OK button. 
 
You will see a new row added to the database table.
 
employeetable5.gif
Figure 5
 
Stored Procedure to Check update
 
MasterInsertUpdateDelete -> right-click select Execute Stored Procedure...
 
Execute procedure window will be opened.
 
StatementType = 'Update'
 
employeetable6.gif
Figure 6
 
Click on the OK button.
 
Check employee table with following updated data where id is 7.
 
employeetable7.gif
Figure 7
 
Stored Procedure to Check Delete
 
MasterInsertUpdateDelete -> right-click select Execute Stored Procedure...
 
Execute procedure window will be opened.
 
StatementType = 'Delete'
 
employeetable8.gif
Figure 8
 
We will delete records from the table which has id=2.
 
Click on the OK button. And check in the employee table with the following deleted data where id is 2.
 
employeetable9.gif
Figure 9
 

Summary

 
A single stored procedure can be used to select, add, update, and delete data from a database table. In this article, we learned how to create a single stored procedure to perform all operations using a single SP in SQL Server.