SpDev Install free

How to write a stored procedure in SQL Server: a template that holds up

A stored procedure template for SQL Server with SET options, parameter checks, a transaction and error handling that works, and the reasons behind each line.

By Areeb Ahmer, . 5 minute read.

CREATE PROCEDURE takes a name and a body, and almost anything compiles. The difference between a procedure that works in a demo and one that survives production is in a handful of lines most examples leave out: what happens on an error, whether a half-finished change can be committed, what the caller learns when something fails.

This article builds a procedure step by step, then gives the template. The examples run against the SpDevDemo database.

The template

CREATE OR ALTER PROCEDURE dbo.CancelOrder
    @OrderId INT,
    @Reason  NVARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    -- 1. Check the input before touching anything.
    IF @OrderId IS NULL OR @Reason IS NULL OR @Reason = N''
        THROW 50001, 'OrderId and Reason are required.', 1;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 2. Lock the row you will change, and check its state.
        DECLARE @Status VARCHAR(20);
        SELECT @Status = Status
        FROM dbo.Orders WITH (UPDLOCK, HOLDLOCK)
        WHERE OrderId = @OrderId;

        IF @Status IS NULL
            THROW 50002, 'The order does not exist.', 1;
        IF @Status IN ('Shipped', 'Delivered')
            THROW 50003, 'A shipped order cannot be cancelled.', 1;

        -- 3. The work.
        UPDATE dbo.Orders SET Status = 'Cancelled' WHERE OrderId = @OrderId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;   -- pass the original error to the caller
    END CATCH
END

Try it:

DECLARE @open INT = (SELECT TOP (1) OrderId FROM dbo.Orders WHERE Status = 'Open' ORDER BY OrderId);
EXEC dbo.CancelOrder @OrderId = @open, @Reason = N'Customer asked';
SELECT OrderId, Status FROM dbo.Orders WHERE OrderId = @open;

EXEC dbo.CancelOrder @OrderId = 1, @Reason = N'Too late';
OrderId  Status
-------  ---------
96       Cancelled

Msg 50003, Level 16, State 1, Procedure dbo.CancelOrder, Line 25
A shipped order cannot be cancelled.

Now, line by line, why it looks like this.

CREATE OR ALTER, with the schema

CREATE OR ALTER (SQL Server 2016 SP1 and later) keeps the procedure’s permissions when you redeploy it; dropping and recreating it loses them. Always name the schema, both for the procedure and for every table inside it: dbo.Orders, not Orders. An unqualified name is resolved against the caller’s default schema first, which costs a lookup and can pick up a different table for a user whose default schema isn’t dbo.

Don’t start names with sp_. SQL Server treats that prefix as a system procedure: it looks in master first, and a future system procedure with the same name would win.

SET NOCOUNT ON

Without it, every statement sends a “rows affected” message to the client. Applications ignore them, but they cost a little traffic, and some older client libraries mistake them for result sets.

SET XACT_ABORT ON

This is the line most procedures are missing. By default, many runtime errors end only the statement that failed. The procedure carries on, and a later COMMIT saves half the work:

CREATE TABLE dbo.Demo (Id INT PRIMARY KEY);
GO
SET XACT_ABORT OFF;
BEGIN TRAN;
    INSERT dbo.Demo VALUES (1);
    INSERT dbo.Demo VALUES (1);   -- fails: duplicate key
    INSERT dbo.Demo VALUES (2);
COMMIT;
SELECT COUNT(*) AS RowsSaved FROM dbo.Demo;
Msg 2627, Level 14, State 1
Violation of PRIMARY KEY constraint ... The duplicate key value is (1).
RowsSaved
---------
2

The transaction “succeeded” with two of its three inserts. With SET XACT_ABORT ON, the error rolls the whole transaction back. It also makes the transaction safe when the client gives up: if an application times out a call, it stops waiting, but SQL Server would otherwise keep the transaction open, holding its locks, until the connection is closed or reused.

Inside a procedure, SET XACT_ABORT ON lasts until the procedure returns; it doesn’t change the caller’s setting.

Check the input first

Validate parameters before you open a transaction, and fail with a clear message. THROW with a number of 50000 or above raises your own error with severity 16; the caller can tell your errors apart by their number.

Lock what you’re about to change

The template reads the order’s status, decides, then updates it. Between the read and the update, another session could change the same order. WITH (UPDLOCK, HOLDLOCK) on the read takes an update lock and keeps it until the transaction ends, so nobody else can change the row in between. Without it, two sessions can both read “Open” and both act on it.

TRY…CATCH that rethrows

The CATCH block does two things: rolls back if a transaction is still open, and rethrows the original error with THROW. Rethrowing matters. A CATCH block that only rolls back, or that returns a status code nobody checks, turns a failure into silence. The caller should get the real error, with its number and line, and decide what to do.

Check @@TRANCOUNT > 0 before ROLLBACK: if the error happened before BEGIN TRANSACTION, or XACT_ABORT already rolled back, there’s nothing to roll back, and ROLLBACK would raise another error.

Things to leave out

  • WITH RECOMPILE on the whole procedure. If one statement suffers from parameter sniffing, put OPTION (RECOMPILE) on that statement only. See parameter sniffing.
  • SELECT *. List the columns. A new column in the table shouldn’t change what your procedure returns.
  • WITH (NOLOCK) “for speed”. It reads uncommitted data and can return rows twice or not at all. See NOLOCK.
  • Catching errors to return codes. Unless every caller checks the code, use THROW.
  • Nested BEGIN TRANSACTION in called procedures that assume they own the transaction. See transactions for how nesting really works.

Before you deploy it

  1. Keep the procedure in source control as a .sql file, with CREATE OR ALTER so the file can be run again.
  2. Run it with typical and extreme parameter values, and look at the actual plan for each.
  3. Test the error paths: missing input, a row in the wrong state, a constraint violation. Check that nothing is left half done.

SpDev runs and steps through procedures from exactly that .sql file, without deploying it first. Its sandbox mode runs a procedure inside a transaction that is rolled back afterwards, so you can test the paths that change data as often as you like.