SQL Server Error Handling: TRY...CATCH, THROW and Transactions

Error handling in T-SQL decides whether a failure leaves your data clean or half-updated, and whether the application gets a clear error or a mysterious timeout from a transaction left open. A small, consistent pattern covers almost every stored procedure.

SQL Server error handling with TRY CATCH and THROW

The template

CREATE OR ALTER PROCEDURE dbo.PostPayrollRun
    @PayrollRunId INT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE dbo.PayrollRun
        SET Status = 'Posted'
        WHERE PayrollRunId = @PayrollRunId;

        INSERT INTO dbo.PayrollAudit (PayrollRunId, Action, CreatedAt)
        VALUES (@PayrollRunId, 'Posted', SYSUTCDATETIME());

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        THROW;   -- re-raise the original error to the caller
    END CATCH
END;

Each piece has a job. Here is why.

SET XACT_ABORT ON

By default, some errors (like many constraint violations) abort only the failing statement, and the transaction carries on. That is how you get half-finished work. With XACT_ABORT ON, most run-time errors roll back the entire transaction. It also matters when a client times out and cancels a call: without it, the transaction can stay open on the server, holding locks, until the connection is reused or closed.

XACT_STATE() before ROLLBACK

  • 1: there is an active, committable transaction.
  • -1: there is a transaction, but it is doomed; it can only be rolled back.
  • 0: no transaction is open.

Checking XACT_STATE() <> 0 means you roll back whenever there is something to roll back, and never call ROLLBACK when there is no transaction (which would raise a second error).

THROW instead of RAISERROR

THROW; with no arguments re-raises the original error with its number, message and line, which is exactly what the caller and your logs need. RAISERROR is older and does not re-raise the original error as cleanly. To raise your own business error, use THROW with a number of 50000 or higher:

IF NOT EXISTS (SELECT 1 FROM dbo.PayrollRun WHERE PayrollRunId = @PayrollRunId)
    THROW 50001, 'Payroll run not found.', 1;

One gotcha: the statement before THROW must end with a semicolon, or SQL Server misreads it.

Log the details

Inside CATCH, these functions describe the error. Log them to a table after the rollback so the log entry is not rolled back too:

BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

    INSERT INTO dbo.ErrorLog (ErrorNumber, ErrorMessage, ErrorProcedure, ErrorLine, LoggedAt)
    VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE(), SYSUTCDATETIME());

    THROW;
END CATCH

What CATCH does not catch

  • Compile errors in the same batch, such as syntax errors or a missing table at compile time, are not caught by that batch's TRY. They are caught by a TRY...CATCH in an outer scope that calls the code (for example, a calling procedure).
  • Client timeouts and cancellations stop the batch from the client side, so the CATCH block never runs. This is exactly why XACT_ABORT ON matters.
  • Errors with low severity (10 or lower) are informational messages, not errors that jump to CATCH.

Handling deadlocks

A deadlock victim gets error 1205, which CATCH does see. Rather than retrying inside the procedure, it is usually cleaner to let the error reach the application and retry the whole operation there.

Quick checklist

  • SET XACT_ABORT ON at the top of every procedure that changes data.
  • Wrap the transaction in TRY...CATCH.
  • Roll back when XACT_STATE() <> 0, then log, then THROW;.
  • Use THROW 50000+ for business rule errors.
  • Handle retries for deadlocks and transient errors in the application.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Image
When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...
Read more