Best Stored Procedure For CRUD Operations

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 :)

 

Create Operation

IF @action = 'INSERT'
 BEGIN
  INSERT INTO Food (
  [name],[type],[dateCreated],[dateUpdated],[isActive] ) VALUES (
  @name,@type,GETDATE(),GETDATE(),@isActive)
 END

Read Operation

IF @action = 'SELECT'
 BEGIN
  SELECT [id],[name],[type],[dateCreated],[dateUpdated],[isActive]
  FROM [Food]
  WHERE (@id = 0 OR [id] = @id) AND (@name = '' OR [name] = @name) AND (@type = '' OR [type] = @type)
 END

Update Operation

IF @action = 'UPDATE'
 BEGIN
  UPDATE Food SET
  [name] = ISNULL(ISNULL(NULLIF(@name,' '), NULL), [name]),
  [type] = ISNULL(ISNULL(NULLIF(@type,' '), NULL), [type]),
  [dateUpdated] = GETDATE()
  WHERE [id] = @id
 END

Delete Operation

IF @action = 'DELETE'
 BEGIN
  DELETE FROM Food
  WHERE [id] = @id
 END

Activate/ Deactivate Operation

IF @action = 'ACTIVATE-DEACTIVATE'
 BEGIN
  UPDATE Food SET
  [isActive] = @isActive
  WHERE [id] = @id
 END

Final Stored Procedure 

CREATE PROCEDURE [dbo].[food_curd_sp] (
@action AS VARCHAR(25) = '',
@id AS INT = 0,
@name AS NVARCHAR(100) ='',
@type AS NVARCHAR(50) ='',
@isActive AS BIT = 1
) AS

BEGIN
 IF @action = 'SELECT'
 BEGIN
  SELECT [id],[name],[type],[dateCreated],[dateUpdated],[isActive]
  FROM [Food]
  WHERE (@id = 0 OR [id] = @id) AND (@name = '' OR [name] = @name) AND (@type = '' OR [type] = @type)
 END
 ELSE IF @action = 'INSERT'
 BEGIN
  INSERT INTO Food (
  [name],[type],[dateCreated],[dateUpdated],[isActive] ) VALUES (
  @name,@type,GETDATE(),GETDATE(),@isActive)
 END
 ELSE IF @action = 'UPDATE'
 BEGIN
  UPDATE Food SET
  [name] = ISNULL(ISNULL(NULLIF(@name,' '), NULL), [name]),
  [type] = ISNULL(ISNULL(NULLIF(@type,' '), NULL), [type]),
  [dateUpdated] = GETDATE()
  WHERE [id] = @id
 END
 ELSE IF @action = 'ACTIVATE-DEACTIVATE'
 BEGIN
  UPDATE Food SET
  [isActive] = @isActive
  WHERE [id] = @id
 END
 ELSE IF @action = 'DELETE'
 BEGIN
  DELETE FROM Food
  WHERE [id] = @id
 END
END

Result

 

Select Operation 

EXEC [dbo].[food_curd_sp] @action = 'SELECT'
EXEC [dbo].[food_curd_sp] @action = 'SELECT', @id = 5
EXEC [dbo].[food_curd_sp] @action = 'SELECT', @type = 'Fast Food'
EXEC [dbo].[food_curd_sp] @action = 'SELECT', @type = 'Fast Food', @name='Burger'

Insert Operation

EXEC [dbo].[food_curd_sp] @action = 'INSERT', @name='Lemonade', @type ='Beverage'

Update Operation 

-- Update only name
EXEC [dbo].[food_curd_sp] @action = 'UPDATE', @id = 13, @name='Mint Lemonade'
-- Update only type
EXEC [dbo].[food_curd_sp] @action = 'UPDATE', @id = 13, @type='Cold Drink'
-- Update both name & type
EXEC [dbo].[food_curd_sp] @action = 'UPDATE', @id = 13, @name='Lemonade', @type='Cold Drink'

Activate/ De-Activate

-- Activate
EXEC [dbo].[food_curd_sp] @action = 'ACTIVATE-DEACTIVATE', @id = 1, @isActive = 1
-- De-Activate
EXEC [dbo].[food_curd_sp] @action = 'ACTIVATE-DEACTIVATE', @id = 1, @isActive = 0

Delete

-- Delete By Id
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 :)