Single Stored Procedure For 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:

  1. CREATE PROC USP_DML_EMPLOYEE (  
  2. @EMP_ID INT,  
  3. @EMP_NAME NVARCHAR(MAX),  
  4. @EMP_SALARY INT,  
  5. @EMP_CITY NVARCHAR(MAX),  
  6. @QUERY_TYPE INT  
  7. AS BEGIN IF @QUERY_TYPE = 1 BEGIN INSERT INTO Employee (  
  8. Emp_ID, Emp_Name, Emp_Salary, Emp_City  
  9. )  
  10. VALUES  
  11. (  
  12. @EMP_ID, @EMP_NAME, @EMP_SALARY, @EMP_CITY  
  13. END ELSE IF @QUERY_TYPE = 2 BEGIN  
  14. UPDATE  
  15. Employee  
  16. SET  
  17. Emp_Name = @EMP_NAME,  
  18. Emp_Salary = @EMP_SALARY,  
  19. @EMP_CITY = @EMP_CITY  
  20. WHERE  
  21. Emp_ID = @EMP_ID END ELSE IF @QUERY_TYPE = 3  
  22. DELETE FROM  
  23. Employee  
  24. WHERE  
  25. 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.
Ebook Download
View all
Learn
View all