Introduction

In this blog, I will show you how to write multiple SQL Statements in one procedure.

Instead of writing separate procedures for Select, Insert, Delete and Update operations into the table, I am going to show you how to combine all operations into one single Stored Procedure.

This Table is called tblEmployee with the below structure:

We want to write a procedure for this table for Select, Insert, Delete and Update records.

Instead of writing separate a stored procedure for each operation we are going to write only one stored procedure to perform Select, Insert, Delete and Update records.

How To Achieve It

Are you wondering how to accomplish this? It is simple -- just add a parameter to the stored procedure. Depending on this parameter we are going to execute the appropriate operations.

Here is the stored procedure:

  1. Createprocedure [dbo].[USP_Employee] @empid asint=0, @empname asvarchar(50)=NULL, @age asint=0, @salary asint=0, @dob asvarchar(20)=NULL, @designation asvarchar(50)=NULL, @Reqtype asvarchar(10)=NULL AS
  2. BEGINIF @Reqtype='SELECT'
  3. BEGIN
  4. SELECT empid,
  5. empname,
  6. age,
  7. salary,
  8. dob,
  9. designation
  10. FROM tblemployee
  11. ENDIF @Reqtype='INSERT'
  12. BEGIN
  13. insertinto tblemployee VALUES(@empid,@empname,@age,@salary,@dob,@designation)
  14. ENDIF @Reqtype='DELETE'
  15. BEGIN
  16. deletefrom tblemployee WHERE empid=@empid
  17. ENDIF @Reqtype='UPDATE'
  18. BEGIN
  19. UPDATE tblemployee
  20. SET empname=@empname,
  21. age=@age,
  22. salary=@salary,
  23. dob=@dob,
  24. designation=@designation
  25. WHERE empid=@empid
  26. ENDEND
In the above example, based on the @Reqtype column the corresponding sql command will execute. For example, if the @Reqtype is select then select statement will execute. If the @Reqtype is inserted then Insert statement will execute.

Summary

In this blog, we have learned how to write multiple SQL operations into a single SQL procedure.