Single Stored Procedure For DML Queries

In this blog we will learn how to create a single stored procedure for all DML queries.

We will use the stored procedure for retrieving, inserting, and deleting data from table. We will create separate stored procedure for Insert, Delete, and Update operation. But suppose we have 1000 of tables, if we create separate stored procedure for each DML query (Insert, Update, Delete), then it will take a large space in Context menu and it will be very difficult to search any specific stored procedure.

To solve this problem we can create a common stored procedure for DML query that contain one extra parameter for recognize the type of query. This approach will save our space and time.

Now we will take an example how can we achieve this approach.

I have a table Employee . Now I will create a stored procedure for this  table that contains all queries of DML.

Stored Procedure is:

CREATE PROC USP_DML_EMPLOYEE (
@EMP_ID INT,
@EMP_NAME NVARCHAR(MAX),
@EMP_SALARY INT,
@EMP_CITY NVARCHAR(MAX),
@QUERY_TYPE INT
) AS BEGIN IF @QUERY_TYPE = 1 BEGIN INSERT INTO Employee (
Emp_ID, Emp_Name, Emp_Salary, Emp_City
)
VALUES
(
@EMP_ID, @EMP_NAME, @EMP_SALARY, @EMP_CITY
) END ELSE IF @QUERY_TYPE = 2 BEGIN
UPDATE
Employee
SET
Emp_Name = @EMP_NAME,
Emp_Salary = @EMP_SALARY,
@EMP_CITY = @EMP_CITY
WHERE
Emp_ID = @EMP_ID END ELSE IF @QUERY_TYPE = 3
DELETE FROM
Employee
WHERE
Emp_ID = @EMP_ID END

In this procedure we pass one extra parameter (i.e. @QUERY_TYPE.) This parameter is used for recognizing the type of DML query. If we want to perform insert operation then we pass 1 for Insert , 2 for Update and 3 for Delete.