Stored Procedure Patterns — Upsert, Paging and Audit Logging

Production stored procedures implement recurring patterns: the upsert (update if exists, insert if not), soft deletes (mark as deleted rather than removing), bulk operations via table-valued parameters, and audit logging. These patterns appear across all enterprise applications and are worth implementing correctly from the start — a poorly designed upsert with race conditions causes duplicate rows; a missing audit log makes compliance impossible.

Production Stored Procedure Patterns #

-- ── Upsert pattern — safe insert or update ───────────────────────────────
CREATE OR ALTER PROCEDURE dbo.usp_UpsertTag
    @Name NVARCHAR(50),
    @Slug VARCHAR(50),
    @TagId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    -- Try update first; if no rows affected, insert
    UPDATE dbo.Tags
    SET    Name = @Name
    WHERE  Slug = @Slug;

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO dbo.Tags (Name, Slug)
        VALUES (@Name, @Slug);
        SET @TagId = SCOPE_IDENTITY();
    END
    ELSE
        SELECT @TagId = Id FROM dbo.Tags WHERE Slug = @Slug;
END;
GO

-- ── Table-Valued Parameter — bulk tag upsert for a post ───────────────────
-- Step 1: Create the TVP type in the database
CREATE TYPE dbo.TagNameList AS TABLE (
    TagSlug VARCHAR(50) NOT NULL,
    TagName NVARCHAR(50) NOT NULL
);
GO

-- Step 2: Create procedure using the TVP
CREATE OR ALTER PROCEDURE dbo.usp_SetPostTags
    @PostId  INT,
    @Tags    dbo.TagNameList READONLY   -- TVP must be READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    -- Delete tags not in the new list
    DELETE pt FROM dbo.PostTags pt
    WHERE  pt.PostId = @PostId
      AND  NOT EXISTS (
          SELECT 1 FROM @Tags t
          JOIN dbo.Tags tag ON tag.Slug = t.TagSlug
          WHERE tag.Id = pt.TagId
      );

    -- Insert new tags (ensure the tags exist first)
    MERGE dbo.Tags AS target
    USING @Tags AS source ON target.Slug = source.TagSlug
    WHEN NOT MATCHED THEN
        INSERT (Name, Slug) VALUES (source.TagName, source.TagSlug);

    -- Insert new PostTag associations
    INSERT INTO dbo.PostTags (PostId, TagId)
    SELECT @PostId, t.Id
    FROM   @Tags tv
    JOIN   dbo.Tags t ON t.Slug = tv.TagSlug
    WHERE  NOT EXISTS (
        SELECT 1 FROM dbo.PostTags pt
        WHERE pt.PostId = @PostId AND pt.TagId = t.Id
    );

    COMMIT TRANSACTION;
END;
GO

-- ── Audit logging trigger procedure ──────────────────────────────────────
-- Create audit table first:
-- CREATE TABLE dbo.AuditLog (
--   Id INT IDENTITY, TableName NVARCHAR(100), RecordId INT,
--   Action VARCHAR(10), ChangedBy NVARCHAR(450),
--   OldValues NVARCHAR(MAX), NewValues NVARCHAR(MAX),
--   ChangedAt DATETIME2(7) DEFAULT SYSUTCDATETIME()
-- );

-- Procedure to log a change:
CREATE OR ALTER PROCEDURE dbo.usp_LogAudit
    @TableName  NVARCHAR(100),
    @RecordId   INT,
    @Action     VARCHAR(10),     -- 'INSERT', 'UPDATE', 'DELETE'
    @ChangedBy  NVARCHAR(450),
    @OldValues  NVARCHAR(MAX) = NULL,
    @NewValues  NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.AuditLog (TableName, RecordId, Action, ChangedBy, OldValues, NewValues)
    VALUES (@TableName, @RecordId, @Action, @ChangedBy, @OldValues, @NewValues);
END;

Common Mistakes #

Mistake 1 — Check-first upsert race condition (duplicate key error under concurrent load) #

❌ Wrong — IF EXISTS (UPDATE) ELSE INSERT; two concurrent calls both see no existing row; both INSERT; duplicate key.

✅ Correct — update-first: UPDATE; IF @@ROWCOUNT = 0 INSERT; only one INSERT for new rows.

Mistake 2 — TVP not marked READONLY (compile error) #

❌ Wrong — @Tags dbo.TagNameList without READONLY; SQL Server requires TVP parameters to be READONLY.

✅ Correct — always @Tags dbo.TagNameList READONLY.