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

Practice tip: Run the failing call above yourself and confirm the error message AND the absence of any change. Then try it again with a valid customer_id and confirm the COMMIT path works. Seeing both branches fire for real is what turns “the pattern to memorize” into something you actually understand instead of copy-paste boilerplate.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.

TRY/CATCH Error Handling in SQL Server: THROW vs RAISERROR

Production T-SQL needs to fail gracefully, not just fail. An unhandled error in the middle of a multi-step operation can leave data in a half-finished state — TRY/CATCH, paired with transactions (covered in full in Chapter 9), is how you prevent that.

The Pattern

BEGIN TRY
    UPDATE dbo.Product SET stock_qty = stock_qty - 10 WHERE product_id = 2;
    IF (SELECT stock_qty FROM dbo.Product WHERE product_id = 2) < 0
        THROW 51000, 'Stock quantity cannot go negative.', 1;
END TRY
BEGIN CATCH
    PRINT 'Error caught: ' + ERROR_MESSAGE();
    PRINT 'Error number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
    PRINT 'Error line: ' + CAST(ERROR_LINE() AS VARCHAR(10));
END CATCH;

Code inside BEGIN TRY ... END TRY runs normally. The instant any statement in that block raises an error, execution jumps immediately to BEGIN CATCH ... END CATCH — the rest of the TRY block is skipped entirely, similar to try/catch in C#, Java, or Python, but with SQL-Server-specific error inspection functions.

THROW vs RAISERROR

THROW is preferred — it's simpler, and with no arguments inside a CATCH block, it correctly preserves the original error's number, severity, and state when re-thrown. Reach for RAISERROR only when you need its legacy formatting (%s/%d placeholders) or must support very old SQL Server versions.

-- Re-throwing the ORIGINAL caught error, unchanged, after logging it:
BEGIN TRY
    SELECT 1/0; -- deliberate divide-by-zero to trigger an error
END TRY
BEGIN CATCH
    PRINT 'Logged: ' + ERROR_MESSAGE();
    THROW; -- bare THROW re-raises the exact original error
END CATCH;

Common mistake: Calling THROW with your own custom message/number when you meant to just re-raise the original error for the caller to see. A bare THROW; (no arguments) inside CATCH re-throws exactly what was caught — THROW 50000, 'Something failed', 1; replaces it with a brand-new, less specific error that loses the original diagnostic detail.

The Error Functions Toolkit

Function	Returns
`ERROR_NUMBER()`	The error's numeric code
`ERROR_MESSAGE()`	The human-readable error text
`ERROR_LINE()`	Line number where the error occurred
`ERROR_PROCEDURE()`	Procedure/function name, NULL if ad-hoc
`ERROR_SEVERITY()`	Severity level (11-19 typical for handleable errors)
`ERROR_STATE()`	A custom state number you can use to distinguish similar errors

All six are only valid inside a CATCH block — called anywhere else, they simply return NULL, since there's no error context to describe.

Nesting: A TRY/CATCH Inside a CATCH

BEGIN TRY
    UPDATE dbo.Product SET stock_qty = stock_qty - 10 WHERE product_id = 2;
END TRY
BEGIN CATCH
    BEGIN TRY
        INSERT INTO dbo.ErrorLog (error_message, logged_at) VALUES (ERROR_MESSAGE(), SYSDATETIME());
    END TRY
    BEGIN CATCH
        PRINT 'Even the error logging failed — this is genuinely bad, escalate.';
    END CATCH;
    THROW;
END CATCH;

This is a real, defensible pattern in production code: log the error to a table for later diagnosis, but wrap the logging itself in its own TRY/CATCH — you don't want a failure in your error-logging code to mask or replace the original error.

Practice tip: Trigger three different real errors on purpose — a divide by zero, a constraint violation, and a THROW with a custom message — and print all six ERROR_ functions for each inside a CATCH block. Seeing how the values differ across error types builds real intuition faster than reading the reference table above.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it's published, and share it with a developer who'd find it useful.

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.

Tag: Error Handling