SQL‑Native Stored Procedure Versioning (POC)

One‑Page Summary

Purpose of This Versioning POC: This POC validated that SQL Server can support a lightweight, clean, self‑contained versioning system for stored procedures that stores version metadata outside the procedure, tracks deployment history, requires no code changes, works in a sandbox, and is seamless to developers. Versioning is treated as its own workflow — separate from inventory or usage analysis.

Clarification: Extended Properties + Version Table Work Together

Extended properties store the current version label, while the version table stores all historical versions including version deltas. Together:

• Extended property = current version
• Version table = full audit trail

This hybrid model is the recommended best practice.

1 Step 1 — Extended Metadata (Current Version Label)

Version metadata was added directly to stored procedure objects using extended properties to track the current version number.

Add Version Metadata:

EXEC sys.sp_addextendedproperty  
    @name = N'Version',
    @value = N'1.0.0',
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'PROCEDURE', @level1name = 'usp_ProcessClaims';

Bulk Apply Extended Properties (when ready!):

DECLARE @proc SYSNAME, @schema SYSNAME, @sql NVARCHAR(MAX);
DECLARE cur CURSOR FOR
SELECT s.name, p.name
FROM sys.procedures p
JOIN sys.schemas s ON p.schema_id = s.schema_id;

OPEN cur;
FETCH NEXT FROM cur INTO @schema, @proc;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = '
        BEGIN TRY
            EXEC sys.sp_addextendedproperty 
                @name = N''Version'',
                @value = N''1.0.0'',
                @level0type = N''SCHEMA'', @level0name = ''' + @schema + ''',
                @level1type = N''PROCEDURE'', @level1name = ''' + @proc + ''';
        END TRY
        BEGIN CATCH
            EXEC sys.sp_updateextendedproperty 
                @name = N''Version'',
                @value = N''1.0.0'',
                @level0type = N''SCHEMA'', @level0name = ''' + @schema + ''',
                @level1type = N''PROCEDURE'', @level1name = ''' + @proc + ''';
        END CATCH;
    ';
    EXEC (@sql);

    FETCH NEXT FROM cur INTO @schema, @proc;
END
CLOSE cur;
DEALLOCATE cur;

2 Step 2 — Version History Table

A StoredProcedureVersionHistory table was created to capture version deltas and audit metadata.

Correct Table Definition (Final):

CREATE TABLE [dbo].[StoredProcedureVersionHistory](
    [HistoryID] [int] IDENTITY(1,1) NOT NULL,
    [ProcedureName] [sysname] NOT NULL,
    [SchemaName] [sysname] NOT NULL,
    [OldVersion] [nvarchar](50) NULL,
    [NewVersion] [nvarchar](50) NULL,
    [ChangedBy] [nvarchar](128) NOT NULL,
    [ChangedOn] [datetime2](7) NOT NULL,
    [Notes] [nvarchar](4000) NULL,
    PRIMARY KEY CLUSTERED ([HistoryID] ASC)
) ON [PRIMARY];
GO

ALTER TABLE [dbo].[StoredProcedureVersionHistory] 
    ADD DEFAULT (suser_sname()) FOR [ChangedBy];
GO

ALTER TABLE [dbo].[StoredProcedureVersionHistory] 
    ADD DEFAULT (sysdatetime()) FOR [ChangedOn];
GO

3 Step 3 — DDL Trigger (Corrected to Match Your Schema)

A non‑blocking database‑level trigger was implemented to log version deltas on CREATE, ALTER, or DROP events.

CREATE TRIGGER trg_LogProcedureChanges
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @ProcName SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
        @SchemaName SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
        @EventType NVARCHAR(100) = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)');

    BEGIN TRY
        INSERT INTO dbo.StoredProcedureVersionHistory
        (
            ProcedureName,
            SchemaName,
            OldVersion,
            NewVersion,
            Notes
        )
        VALUES
        (
            @ProcName,
            @SchemaName,
            NULL,               -- OldVersion (optional)
            'AUTO',             -- NewVersion
            @EventType          -- Notes
        );
    END TRY
    BEGIN CATCH
        INSERT INTO dbo.TriggerErrorLog (ErrorMessage)
        VALUES (ERROR_MESSAGE());
    END CATCH
END;

4 Step 4 — Error Logging

A TriggerErrorLog table was added to capture failures without blocking development workflows, meeting VA high‑availability standards.

CREATE TABLE dbo.TriggerErrorLog (
    ErrorID INT IDENTITY(1,1) PRIMARY KEY,
    ErrorMessage NVARCHAR(4000),
    ErrorDate DATETIME DEFAULT GETDATE()
);