Best Stored Procedure For CRUD Operations

In this article, I will show you an approach to get the most out of your Stored Procedure.

What is Stored Procedure?

A stored procedure is a formulated SQL code you can save to keep reusing the code. One or more T-SQL statements into a logical unit are stored in the server database as an object. If a saved procedure is initially called in the SQL Server, it creates a plan and stores it in the cache. SQL Server reuses the plan when executing the saved procedure so, that a reliable performance can be performed very quickly.

What are CRUD Operations?

CRUD operations include the fundamental operations Insert, Read, Update, and Delete.

  • C – CREATE
  • R – READ
  • U – UPDATE
  • D- DELETE

Let’s start with the code :)

Best Stored Procedure For CURD Operations
 

Create Operation

  1. IF @action = 'INSERT'      
  2.  BEGIN      
  3.   INSERT INTO Food (       
  4.   [name],[type],[dateCreated],[dateUpdated],[isActive] ) VALUES (       
  5.   @name,@type,GETDATE(),GETDATE(),@isActive)      
  6.  END 

Read Operation

  1. IF @action = 'SELECT'      
  2.  BEGIN      
  3.   SELECT [id],[name],[type],[dateCreated],[dateUpdated],[isActive]       
  4.   FROM [Food]       
  5.   WHERE (@id = 0 OR [id] = @id) AND (@name = '' OR [name] = @nameAND (@type = '' OR [type] = @type)     
  6.  END 

Update Operation

  1. IF @action = 'UPDATE'      
  2.  BEGIN      
  3.   UPDATE Food SET       
  4.   [name] = ISNULL(ISNULL(NULLIF(@name,' '), NULL), [name]),      
  5.   [type] = ISNULL(ISNULL(NULLIF(@type,' '), NULL), [type]),      
  6.   [dateUpdated] = GETDATE()      
  7.   WHERE [id] = @id      
  8.  END 

Delete Operation

  1. IF @action = 'DELETE'      
  2.  BEGIN      
  3.   DELETE FROM Food       
  4.   WHERE [id] = @id      
  5.  END 

Activate/ Deactivate Operation

  1. IF @action = 'ACTIVATE-DEACTIVATE'      
  2.  BEGIN      
  3.   UPDATE Food SET        
  4.   [isActive] = @isActive       
  5.   WHERE [id] = @id      
  6.  END 

Final Stored Procedure 

  1. CREATE PROCEDURE [dbo].[food_curd_sp] (       
  2. @action AS VARCHAR(25) = '',      
  3. @id AS INT = 0,      
  4. @name AS NVARCHAR(100) ='',      
  5. @type AS NVARCHAR(50) ='',      
  6. @isActive AS BIT = 1      
  7. AS      
  8.     
  9. BEGIN      
  10.  IF @action = 'SELECT'      
  11.  BEGIN      
  12.   SELECT [id],[name],[type],[dateCreated],[dateUpdated],[isActive]       
  13.   FROM [Food]       
  14.   WHERE (@id = 0 OR [id] = @id) AND (@name = '' OR [name] = @nameAND (@type = '' OR [type] = @type)     
  15.  END      
  16.  ELSE IF @action = 'INSERT'      
  17.  BEGIN      
  18.   INSERT INTO Food (       
  19.   [name],[type],[dateCreated],[dateUpdated],[isActive] ) VALUES (       
  20.   @name,@type,GETDATE(),GETDATE(),@isActive)      
  21.  END      
  22.  ELSE IF @action = 'UPDATE'      
  23.  BEGIN      
  24.   UPDATE Food SET       
  25.   [name] = ISNULL(ISNULL(NULLIF(@name,' '), NULL), [name]),      
  26.   [type] = ISNULL(ISNULL(NULLIF(@type,' '), NULL), [type]),      
  27.   [dateUpdated] = GETDATE()      
  28.   WHERE [id] = @id      
  29.  END      
  30.  ELSE IF @action = 'ACTIVATE-DEACTIVATE'      
  31.  BEGIN      
  32.   UPDATE Food SET        
  33.   [isActive] = @isActive       
  34.   WHERE [id] = @id      
  35.  END     
  36.  ELSE IF @action = 'DELETE'      
  37.  BEGIN      
  38.   DELETE FROM Food       
  39.   WHERE [id] = @id      
  40.  END      
  41. END

Result

 
Select Operation 
  1. EXEC [dbo].[food_curd_sp] @action = 'SELECT'    
  2. EXEC [dbo].[food_curd_sp] @action = 'SELECT', @id = 5    
  3. EXEC [dbo].[food_curd_sp] @action = 'SELECT', @type = 'Fast Food'    
  4. EXEC [dbo].[food_curd_sp] @action = 'SELECT', @type = 'Fast Food', @name='Burger' 
Best Stored Procedure For CURD Operations

Insert Operation

  1. EXEC [dbo].[food_curd_sp] @action = 'INSERT', @name='Lemonade', @type ='Beverage' 
Update Operation 
  1. -- Update only name    
  2. EXEC [dbo].[food_curd_sp] @action = 'UPDATE', @id = 13, @name='Mint Lemonade'    
  3. -- Update only type    
  4. EXEC [dbo].[food_curd_sp] @action = 'UPDATE', @id = 13, @type='Cold Drink'    
  5. -- Update both name & type    
  6. EXEC [dbo].[food_curd_sp] @action = 'UPDATE', @id = 13, @name='Lemonade', @type='Cold Drink' 
Activate/ De-Activate
  1. -- Activate    
  2. EXEC [dbo].[food_curd_sp] @action = 'ACTIVATE-DEACTIVATE', @id = 1, @isActive = 1    
  3. -- De-Activate    
  4. EXEC [dbo].[food_curd_sp] @action = 'ACTIVATE-DEACTIVATE', @id = 1, @isActive = 0 
Delete
  1. -- Delete By Id    
  2. EXEC [dbo].[food_curd_sp] @action = 'DELETE', @id=10 

Benefits of using this approach

 
With the help of this syntax, you don't have to write multiple queries with different WHERE clauses in case you want to read the data from a table. Also, if you want to update a few columns for each task, then you don't have to write a separate query for the task.
 
Note
This approach is still not perfect. If you optimize this, please describe your changes in the Comments section :)