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. 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.   
  5. first_name VARCHAR(10),  
  6.   
  7. last_name VARCHAR(10),  
  8.   
  9. salary DECIMAL(10,2),  
  10.   
  11. city VARCHAR(20),  
  12.   
  13. )   

Now insert some values in the table and using select statement to select a table.

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

Table looks like this.

employeetable.gif

Figure 1

Stored procedure for Select, insert, update, delete

Here, we create a stored procedure for select,insert,update,delete statements to select the data from the table.

  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.

Now open object explorer and select storeprocedure MasterInsertUpdateDelete.

Stored Procedure to Check Insert

StatementType = 'Insert'

MasterInsertUpdateDelete -> right click select execute stored procedure...

employeetable1.gif

Figure2

Execute procedure window will be open.

employeetable3.gif

Figure3

Now for insert we fill the data in required field.

StatementType=insert

employeetable4.gif

Figure4

Click on the ok Button. and check in the employee table with following inserted data.

employeetable5.gif

Figure5

Stored Procedure to Check update

MasterInsertUpdateDelete -> right click select execute stored procedure...

Execute procedure window will be open.

StatementType = 'Update'

employeetable6.gif

Figure6

Click on the ok Button. and check in the employee table with following updated data where id is 7.

employeetable7.gif

Figure7

Stored Procedure to Check Delete

MasterInsertUpdateDelete -> right click select execute stored procedure...

Execute procedure window will be open.

StatementType = 'Delete'

employeetable8.gif

Figure8

we delete record from 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
Figure9