Error Handling with TRY/CATCH

Analogy categoryAnalogies

Basic TRY...CATCH Structure

A BEGIN TRY...END TRY block wraps statements you want monitored for errors; if any statement inside raises an error with severity 11 through 19 (most common runtime errors), control immediately transfers to the paired BEGIN CATCH...END CATCH block instead of terminating the batch. Errors of severity 20 and above are typically fatal and terminate the connection regardless of TRY...CATCH, and certain compile-time errors (like an invalid object name known before execution starts) or errors that abort the whole session are also not caught by TRY...CATCH in the same batch.

🏏

Cricket analogy: TRY...CATCH is like a third umpire review triggered automatically the instant a contentious decision (severity 11-19) occurs, pausing play to reassess, rather than letting the game continue as if nothing happened.

Inspecting Errors and Re-throwing

Inside a CATCH block, functions like ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), and ERROR_PROCEDURE() report details of the error that was caught, and these values remain accessible for the entire duration of the CATCH block, not just the first statement. THROW (without arguments), when used inside a CATCH block, re-raises the original error with its original error number, message, and state, which is the modern, correct way to propagate an error upward — the older RAISERROR-based re-throw pattern loses some original error context and is now considered legacy.

🏏

Cricket analogy: ERROR_MESSAGE() reporting exact failure details is like Hawk-Eye's ball-tracking readout giving the precise pitching point and trajectory data behind a given lbw decision, not just a bare 'out' verdict.

sql

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE dbo.Accounts SET Balance = Balance - 500 WHERE AccountId = 1;
    UPDATE dbo.Accounts SET Balance = Balance + 500 WHERE AccountId = 2;

    IF (SELECT Balance FROM dbo.Accounts WHERE AccountId = 1) < 0
        THROW 51000, 'Insufficient funds for transfer.', 1;

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

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

    THROW;
END CATCH;

Transactions Inside TRY...CATCH

XACT_STATE() reports whether the current transaction is committable (1), uncommittable (-1, meaning an error left it in a doomed state that only allows ROLLBACK), or that no transaction is active (0) — checking it in a CATCH block before deciding to ROLLBACK is more reliable than assuming @@TRANCOUNT alone tells you the transaction is still usable. Some errors (like certain conversion errors or deadlocks after XACT_ABORT is on) automatically put the transaction into an uncommittable state, so attempting further work inside that same transaction before rolling back will simply raise more errors.

🏏

Cricket analogy: XACT_STATE() = -1 is like a match being abandoned outright due to bad light — no amount of continuing play can salvage the innings, the only valid action is calling it and recording the abandonment.

SET XACT_ABORT ON at the top of a procedure ensures that any run-time error automatically rolls back the entire transaction and terminates the batch, which pairs well with TRY...CATCH for reliable transactional cleanup — without it, some errors leave the transaction open in an uncommittable state that you must explicitly detect and roll back yourself.

Not all errors are caught by TRY...CATCH in the same session — severity 20+ errors terminate the connection, and some errors (like a query timeout raised by the client, or certain compile errors detected before execution begins) bypass CATCH entirely. Never assume TRY...CATCH alone guarantees your batch will always complete gracefully.

BEGIN TRY...END TRY / BEGIN CATCH...END CATCH intercepts errors of severity 11-19 and transfers control to CATCH.
ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE() describe the caught error inside CATCH.
THROW with no arguments inside CATCH re-raises the original error with full original context; this is preferred over legacy RAISERROR re-throw patterns.
XACT_STATE() tells you whether the current transaction is committable (1), doomed/uncommittable (-1), or absent (0).
SET XACT_ABORT ON makes any runtime error automatically roll back the full transaction, simplifying cleanup logic.
Severity 20+ errors and some compile-time or client-side errors are not caught by TRY...CATCH.
Always check XACT_STATE() in CATCH before deciding whether/how to ROLLBACK, rather than assuming @@TRANCOUNT alone is sufficient.

Practice what you learned

1. What severity range of errors does TRY...CATCH typically intercept?

2. What does THROW with no arguments do when used inside a CATCH block?

3. What does XACT_STATE() returning -1 indicate?

4. What does SET XACT_ABORT ON accomplish?

5. Which of the following is generally NOT caught by TRY...CATCH in the same batch?

Was this page helpful?

Triggers in SQL Server

Related Study Notes in Programming

Browse all study notes

Programming

Apache Spark Study Notes

Programming · 30 topics

Programming

Apache Flink Study Notes

Programming · 30 topics

Programming

Hadoop Study Notes

Programming · 30 topics

Programming

Snowflake Study Notes

Programming · 30 topics

Programming

Apache Airflow Study Notes

Programming · 30 topics

Programming

dbt (Data Build Tool) Study Notes

Programming · 30 topics

Topics covered

#Programming#SQLServerTSQLStudyNotes#ErrorHandlingWithTRYCATCH#Error#Handling#TRY#CATCH#ErrorHandling#StudyNotes#SkillVeris

Basic TRY...CATCH Structure

Inspecting Errors and Re-throwing

Transactions Inside TRY...CATCH

Practice what you learned

You May Also Like

Related Reading

Related Study Notes in Programming

Topics covered