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.

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