Single SQL Stored Procedure for all CRUD Operations and Change Tracking

By:   |   Updated: 2024-06-04   |   Comments (2)   |   Related: > Stored Procedures


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?


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

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

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]
/****** Object:  StoredProcedure [dbo].[uspPerson]    Script Date: 08/05/2024 14:31:01 ******/
-- =============================================
-- 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
   IF (@PersonName IS NULL OR LEN(@PersonName) = 0) AND (@PersonId > 0)
      SELECT [PersonId]
         FROM [MsSqlTips].[dbo].[Persons]
         WHERE [PersonId] = @PersonId;
         BEGIN TRY
               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))
                     INSERT INTO [dbo].[Persons]
                        VALUES (@PersonName
                     SET @PersonId = SCOPE_IDENTITY();
               -- Deleting a record
               IF @PersonId < 0
                  DELETE FROM [dbo].[Persons]
                     WHERE [PersonId] = ABS(@PersonId);
               -- Tracking the changes
               INSERT INTO [dbo].[PersonsLog]
            -- Returning the changed record
            SELECT [PersonId]
               FROM [MsSqlTips].[dbo].[Persons]
               WHERE [PersonId] = @PersonId;         
         END TRY
         BEGIN CATCH
            IF @@TRANCOUNT > 0
                  ROLLBACK TRANSACTION;
            -- 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;

SQL Statements when Working with the Stored Procedure


Inserting a record for a person named John:

Inserting the record for john

Inserting a record for a person named Mary:

Inserting the record for mary

Select Statement

Select the record with a PersonId = 1:

Selecting a record by its key number


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

Updating the name of 2 from mary


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.

Deleting the record 1 typing it as a negative number

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
Listing all changes done


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Sebastiao Pereira Sebastiao Pereira has over 38 years of healthcare experience, including software development expertise with databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-06-04

Comments For This Article

Friday, June 7, 2024 - 10:50:15 AM - Sebastiao Pereira Back To Top (92305)
Hello Omar, if you intend to access the store procedure from a Web App please change the table [ModifiedBy] [sysname] NULL to [ModifiedBy] [int] NULL and include the variable @ModifiedBy int in the store procedure. Doing so you are controlling who access the store procedure in your Web App.

Thursday, June 6, 2024 - 1:05:24 PM - Omar Back To Top (92301)
Thanks, what is "CURRENT_USER" , it should be the user loged into the application, of course there are many users can log in. How the current user knows the request user from Web app without user parameter ?

get free sql tips
agree to terms