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:
- 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
- BEGINIF @Reqtype='SELECT'
- BEGIN
- SELECT empid,
- empname,
- age,
- salary,
- dob,
- designation
- FROM tblemployee
- ENDIF @Reqtype='INSERT'
- BEGIN
- insertinto tblemployee VALUES(@empid,@empname,@age,@salary,@dob,@designation)
- ENDIF @Reqtype='DELETE'
- BEGIN
- deletefrom tblemployee WHERE empid=@empid
- ENDIF @Reqtype='UPDATE'
- BEGIN
- UPDATE tblemployee
- SET empname=@empname,
- age=@age,
- salary=@salary,
- dob=@dob,
- designation=@designation
- WHERE empid=@empid
- 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.