Post
Ask Question

Multiple SQL Operation In Single Procedure

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.