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.

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] = @name) AND (@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
  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] = @name) AND (@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 :)