The Standard Stored Procedure: Why Every Proc I Write Starts With the Same 30 Lines

Shannon Lowder

Shannon Lowder

— 3 min read

Every stored procedure I inherit tells me something about the engineer who wrote it. The ones with no error handling tell me they never had to debug a silent failure at 2 a.m. The ones with a bare EXEC sp_something and no transaction management tell me nobody ever asked what happens when step three fails after step two already committed. The ones with no logging tell me the person who wrote it never had to explain to a stakeholder why a job showed green but the data was wrong.

I have been that engineer. Once. The second time a proc failed silently in production, I sat down and wrote a template I have used ever since.

The 30 Lines That Go in Every Proc

Here is the skeleton. Every stored procedure I write starts here:

CREATE PROCEDURE [dbo].[usp_ProcessName]
    @RunDate    DATE        = NULL,
    @Debug      BIT         = 0
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    -- Default run date to today if not supplied
    IF @RunDate IS NULL SET @RunDate = CAST(GETDATE() AS DATE);

    DECLARE @ProcName   NVARCHAR(128) = OBJECT_NAME(@@PROCID);
    DECLARE @StartTime  DATETIME2     = SYSDATETIME();
    DECLARE @RowCount   INT           = 0;
    DECLARE @ErrMsg     NVARCHAR(4000);

    BEGIN TRY

        -- Log start
        INSERT INTO dbo.ProcLog (ProcName, RunDate, StartTime, Status)
        VALUES (@ProcName, @RunDate, @StartTime, 'Running');

        -- ============================================================
        -- YOUR LOGIC GOES HERE
        -- ============================================================

        SET @RowCount = @@ROWCOUNT;

        -- Log success
        UPDATE dbo.ProcLog
        SET    EndTime  = SYSDATETIME(),
               RowCount = @RowCount,
               Status   = 'Success'
        WHERE  ProcName  = @ProcName
          AND  StartTime = @StartTime;

    END TRY
    BEGIN CATCH

        SET @ErrMsg = ERROR_MESSAGE();

        UPDATE dbo.ProcLog
        SET    EndTime    = SYSDATETIME(),
               Status     = 'Failed',
               ErrorMsg   = @ErrMsg
        WHERE  ProcName   = @ProcName
          AND  StartTime  = @StartTime;

        RAISERROR(@ErrMsg, 16, 1);
        RETURN 1;

    END CATCH;

    RETURN 0;
END;

That is the whole skeleton. Roughly 50 lines once you include the closing END. I said 30 in the title because the structural lines — the ones that cost you nothing to write but save you hours when something breaks — are about 30. The rest is whitespace and comments that make the logic section obvious.

Why Each Part Is There

SET NOCOUNT ON stops the "(N row(s) affected)" messages from polluting return result sets. Any calling application or SSIS package that parses output will thank you. SET XACT_ABORT ON means if anything inside a transaction raises a runtime error, SQL Server rolls back automatically rather than leaving you with a partial commit. These two lines cost nothing and prevent entire categories of subtle bugs.

The @RunDate parameter with a default of NULL — then immediately defaulted to today inside the proc — is a pattern I use on every proc that touches time-partitioned data. You can pass a date for reruns. You do not have to. You never have to remember to pass it for normal execution.

@Debug is there because I eventually add IF @Debug = 1 PRINT ... lines when I am chasing something. Better to wire it in at the start than to add a parameter to a proc that is already called from twelve places.

OBJECT_NAME(@@PROCID) gives you the proc name without hardcoding it. If you rename the proc, the logging still works. This is the kind of thing that matters once and then you never think about it again.

The Logging Table

The skeleton assumes a dbo.ProcLog table. Here is a minimal version:

CREATE TABLE dbo.ProcLog (
    LogID      INT           NOT NULL IDENTITY(1,1) PRIMARY KEY,
    ProcName   NVARCHAR(128) NOT NULL,
    RunDate    DATE          NOT NULL,
    StartTime  DATETIME2     NOT NULL,
    EndTime    DATETIME2     NULL,
    RowCount   INT           NULL,
    Status     NVARCHAR(20)  NOT NULL DEFAULT 'Running',
    ErrorMsg   NVARCHAR(4000) NULL,
    CreatedAt  DATETIME2     NOT NULL DEFAULT SYSDATETIME()
);

Every environment I work in gets this table on day one. When someone asks "did that proc run last night," I have an answer. When it failed, I have an error message. When I need to compare this run's row count to last Tuesday's, I have that too.

The Objection I Hear Every Time

"That's a lot of boilerplate for a two-line proc."

True. But the two-line proc that runs every night in a SQL Agent job is exactly the proc that nobody investigates when it silently returns 0 rows because a temp table it depended on wasn't populated. The logging overhead on a lightweight proc is microseconds. The debugging overhead on an unlogged failure in a production pipeline is hours — plus the conversation with whoever owns the downstream data.

Write the skeleton once. Paste it every time. The five minutes you spend deleting the parts you do not need today is the cheapest insurance in the building.

If you have a different version of this pattern — especially if you have solved the logging problem in a way that does not require a permanent table — I would genuinely like to see it. As always, I'm here to help.

Read more