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.
In this blog, we have learned how to write multiple SQL operations into a single SQL procedure.
European SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.