SQL Stored Procedure for all CRUD Operations and Change Tracking

Problem

When writing a Microsoft SQL Server store procedure, it is normal to have multiple stored procedures for each CRUD operation (Create, Read, Update, Delete) – SELECT, INSERT, UPDATE and DELETE. However, is it possible to simplify this Transact-SQL logic into a single SQL Server stored procedure and include tracking changes? Can you please demonstrate a possible solution?

Solution

Let’s create an example of a simple table to store a list of people in a SQL database with T-SQL code run in SQL Server Management Studio (SSMS).

The first database table will store the data. As you can see, the only column that gets updated is the PersonName.

CREATE TABLE [dbo].[Persons](
   [PersonId]     [int] IDENTITY(1,1) NOT NULL,
   [PersonName]   [nvarchar](10) NOT NULL,
   [ModifiedDate] [datetime] NULL,
   [ModifiedBy]   [sysname] NULL
) ON [PRIMARY]
GO

The second database table will track the changes:

CREATE TABLE [dbo].[PersonsLog](
   [ControlId]    [int] IDENTITY(1,1) NOT NULL,
   [PersonId]     [int] NULL,
   [PersonName]   [nvarchar](10) NULL,
   [ModifiedDate] [datetime] NULL,
   [ModifiedBy]   [sysname] NULL
) ON [PRIMARY]
GO

Rules to Call Stored Procedure

The main table PERSONS has a key column [PersonId], so if we want to:

• INSERT (Create Operation): Use 0 (zero) to indicate that it is new and pass the PersonName.
• SELECT (Read Operation): Use the [PersonId] number and leave the [PersonName] null.
• UPDATE (Update Operation): Use the [PersonId] number and pass the PersonName change.
• DELETE (Delete Operation): Use the [PersonId] number but pass as a negative number.
• TRACK CHANGES: This is done inside the stored procedure for the INSERT, UPDATE and DELETE.

This stored procedure takes 2 input parameters:

• @PersonId
• @PersonName

SQL Stored Procedure Syntax

USE [MsSqlTips]
GO
/****** Object:  StoredProcedure [dbo].[uspPerson]    Script Date: 08/05/2024 14:31:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      SCP
-- Create date: 202400508
-- Description: General Stored Procedure
-- =============================================
CREATE PROCEDURE [dbo].[uspPerson]
               @PersonId int, -- input parameters
               @PersonName nvarchar(70) -- variable name and data type
AS
BEGIN
   SET NOCOUNT ON;
 
   IF (@PersonName IS NULL OR LEN(@PersonName) = 0) AND (@PersonId > 0)
      SELECT [PersonId]
            ,[PersonName]
            ,[ModifiedDate]
            ,[ModifiedBy]
         FROM [MsSqlTips].[dbo].[Persons]
         WHERE [PersonId] = @PersonId;
   ELSE
      BEGIN
         BEGIN TRY
            BEGIN TRANSACTION;
 
               DECLARE @ModifiedDate datetime = GETDATE();
               DECLARE @ModifiedBy sysname = CURRENT_USER;
 
               -- Updating a record
               IF @PersonId > 0
                  UPDATE [dbo].[Persons]
                     SET [PersonName] = @PersonName
                        ,[ModifiedDate] = @ModifiedDate
                        ,[ModifiedBy] = @ModifiedBy
                     WHERE [PersonId] = @PersonId;
      
               -- Inserting a record
               IF (@PersonId = 0 AND 
                  (@PersonName IS NOT NULL AND LEN(@PersonName) > 0))
                  BEGIN
                     INSERT INTO [dbo].[Persons]
                           ([PersonName]
                           ,[ModifiedDate]
                           ,[ModifiedBy])
                        VALUES (@PersonName
                           ,@ModifiedDate
                           ,@ModifiedBy);
 
                     SET @PersonId = SCOPE_IDENTITY();
                  END
 
               -- Deleting a record
               IF @PersonId < 0
                  DELETE FROM [dbo].[Persons]
                     WHERE [PersonId] = ABS(@PersonId);
 
               -- Tracking the changes
               INSERT INTO [dbo].[PersonsLog]
                        ([PersonId]
                        ,[PersonName]
                        ,[ModifiedDate]
                        ,[ModifiedBy])
                  VALUES(@PersonId
                        ,@PersonName
                        ,@ModifiedDate
                        ,@ModifiedBy)
 
            COMMIT TRANSACTION;
 
            -- Returning the changed record
            SELECT [PersonId]
                  ,[PersonName]
                  ,[ModifiedDate]
                  ,[ModifiedBy]
               FROM [MsSqlTips].[dbo].[Persons]
               WHERE [PersonId] = @PersonId;         
 
            RETURN;
         END TRY
         BEGIN CATCH
            IF @@TRANCOUNT > 0
               BEGIN
                  ROLLBACK TRANSACTION;
               END
         
            -- Print error information. 
            PRINT 'Error: ' + CONVERT(varchar(50), ERROR_NUMBER()) +
                 ', Severity: ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
                 ', State: ' + CONVERT(varchar(5), ERROR_STATE()) + 
                 ', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') + 
                 ', Line: ' + CONVERT(varchar(5), ERROR_LINE()) + 
                 ', User: ' + CONVERT(varchar(5), @ModifiedBy);
            PRINT ERROR_MESSAGE();
         END CATCH;
   END
END

SQL Statements when Working with the Stored Procedure

Inserting

Inserting a record for a person named John:

Inserting a record for a person named Mary:

Select Statement

Select the record with a PersonId = 1:

Updating

Changing the record for PersonId = 2 from “Mary” to “Mary Jane”:

Deleting

Delete the record with PersonId = 1. For this example, any value for PersonName could have been used since the delete just needs the PersonId value.

Error Handling

If we try to insert a name with more than 10 characters, the store procedure shows the message below:

Tracking Changes

When listing the content of the Table [PersonsLog], we can check for all the of modifications completed in the main [Persons] table. Here is an explanation of the data:

• ControlId 1 and 2 below are inserted records
• ControlId 3 was the name change for the PersonId = 2
• ControlId 4 was a deletion of the PersonId = 1

Summary

As you can see, this is a way to create one stored procedure to handle multiple tasks. This can definitely be made more robust and add additional code for handling record updates and also for the logging table, but hopefully this gives you an idea of how you might accomplish something like this.

Next Steps

• Check out the following MSSQLTips.com resources:
  • Stored Procedures Tutorial
  • SQL Quick Reference Guide
  • All SQL Server Stored Procedures Tips
  • Basic SQL Cheat Sheet using SELECT, INSERT, DELETE and UPDATE

Sebastiao Pereira

Sebastião Pereira has over 40 years of experience in database development including T-SQL, algorithm design, machine learning and bringing innovative mathematical formulas to SQL Server. He started his career at a transnational fast-moving consumer goods (FMCG) company as an employee then later transitioning into a consultant role. He eventually founded his own company to develop software solutions for the healthcare industry.  Sebastião is a respected award-winning author on MSSQLTips.com extending SQL Server capabilities beyond traditional workloads.

• MSSQLTips Awards
  • Author of the Year – 2025
  • Trendsetter (25+ tips) – 2025
  • Rookie of the Year – 2024