Error Handling and Transactions in SQL Server Stored Procedures: The Pattern to Memorize

Production procedures need to fail safely — rolling back cleanly and surfacing a useful error, not leaving data half-changed. This lesson combines Chapter 1’s TRY/CATCH with the transaction concepts formalized fully in Chapter 9, into the single pattern you’ll reuse in nearly every write-capable procedure you ever write.

The Complete Pattern

CREATE PROCEDURE dbo.usp_UpgradeCustomerTier
    @customerId INT, @newTier NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE dbo.Customer SET tier = @newTier WHERE customer_id = @customerId;

        IF @@ROWCOUNT = 0
            THROW 51010, 'No customer found with the given ID.', 1;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        DECLARE @errMsg NVARCHAR(4000) = ERROR_MESSAGE();
        THROW 51011, @errMsg, 1;
    END CATCH
END;

@@ROWCOUNT is worth calling out on its own — it’s a system variable holding the number of rows affected by the most recent statement, and it resets after nearly every statement, including a PRINT. Check it immediately after the statement it’s meant to describe, or its value won’t mean what you think.

Why @@TRANCOUNT Matters

The pattern to memorize: BEGIN TRY → BEGIN TRANSACTION → do the work → COMMIT, with a CATCH block that checks @@TRANCOUNT > 0 before rolling back, then re-throws or logs the error. This guard matters even more once procedures start calling other procedures: if this procedure was itself called from inside someone else’s already-open transaction, @@TRANCOUNT will be higher than 1, and a naive unconditional ROLLBACK here would undo work the caller is still relying on.

Proving It Actually Rolls Back

-- Deliberately trigger the THROW path and confirm no partial update survives
SELECT tier FROM dbo.Customer WHERE customer_id = 99999; -- confirm this ID doesn't exist first
EXEC dbo.usp_UpgradeCustomerTier @customerId = 99999, @newTier = 'premium';
-- Msg 51011: No customer found with the given ID.
SELECT * FROM dbo.Customer WHERE tier = 'premium' AND customer_id = 99999; -- confirms: nothing changed