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

Here, we will see how to create select, insert, update, delete statements using stored procedure in SQL Server. Let's take a look at a practical example. We create a table.

Creating Table 

  1. CREATE TABLE employee(  
  2. id INTEGER NOT NULL PRIMARY KEY,  
  3. first_name VARCHAR(10),  
  4. last_name VARCHAR(10),  
  5. salary DECIMAL(10,2),  
  6. city VARCHAR(20), 
  7. )   

Now add some rows to the table. We can add new rows using an INSERT INTO SQL satement. Then execute a SELECT SQL query to display all records of the table.

  1. INSERT INTO employee VALUES (2, 'Monu''Rathor',4789,'Agra');  
  2. GO  
  3.   
  4. INSERT INTO employee VALUES (4, 'Rahul' , 'Saxena', 5567,'London');  
  5. GO  
  6.   
  7. INSERT INTO employee VALUES (5, 'prabhat''kumar', 4467,'Bombay'); 
  8. go  
  9.   
  10. INSERT INTO employee VALUES (6, 'ramu''kksingh', 3456, 'jk');  
  11. go  
  12.   
  13. select * from employee   

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 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  
  2. (  
  3. @id INTEGER,  
  4. @first_name VARCHAR(10),  
  5. @last_name VARCHAR(10),  
  6. @salary DECIMAL(10,2),  
  7. @city VARCHAR(20),  
  8. @StatementType nvarchar(20) = ''  
  9. )  
  10. AS  
  11. BEGIN  
  12. IF @StatementType = 'Insert'  
  13. BEGIN  
  14. insert into employee (id,first_name,last_name,salary,city) values( @id, @first_name, @last_name, @salary, @city)  
  15. END  
  16. IF @StatementType = 'Select'  
  17. BEGIN  
  18. select * from employee  
  19. END  
  20. IF @StatementType = 'Update'  
  21. BEGIN  
  22. UPDATE employee SET  
  23. First_name = @first_name, last_name = @last_name, salary = @salary,  
  24. city = @city  
  25. WHERE id = @id  
  26. END  
  27. else IF @StatementType = 'Delete'  
  28. BEGIN  
  29. DELETE FROM employee WHERE id = @id  
  30. END  
  31. 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 in 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 record from the table which has id=2.

Click on the OK Button. And check in the employee table with 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.