In Focus

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

Here, we will see how to create select, insert, update, delete SQL statements in stored procedures in SQL.

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.