<style> .reveal-up, .reveal-left, .reveal-right, .reveal-stagger > * { opacity: 1 !important; transform: none !important; } </style>
  1. Learn
  2. SQL
  3. Programmability
All lessons in this course

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Lesson 40 of 54 19 minAdvanced

Error handling: TRY...CATCH and THROW

What SQL Server does when a statement fails, why a failed statement does not stop the batch or undo a transaction, TRY … CATCH and the ERROR_ functions, THROW and RAISERROR, XACT_ABORT and doomed transactions, the errors CATCH cannot see, and a procedure template that gets it right.

BuildMart should never sell stock it does not have. Add a CHECK constraint so that Stock can never go below zero. The rest of this lesson tries to break it.

Query.sql
ALTER TABLE dbo.Products DROP CONSTRAINT IF EXISTS CK_Products_Stock;ALTER TABLE dbo.Products ADD CONSTRAINT CK_Products_Stock CHECK (Stock >= 0);

By default, an error does not stop the script

A customer buys 2 cordless drills (12 in stock) and 5 boxes of wall plugs (none in stock). Run the two updates as a plain script:

Query.sql
UPDATE dbo.Products SET Stock = Stock - 2 WHERE ProductId = 2;  -- Cordless drillUPDATE dbo.Products SET Stock = Stock - 5 WHERE ProductId = 9;  -- Wall plugsPRINT N'Script finished'; SELECT ProductId, Name, Stock FROM dbo.Products WHERE ProductId IN (2, 9);
Output
Msg 547, Level 16, State 0, Line 2
The UPDATE statement conflicted with the CHECK constraint "CK_Products_Stock". The conflict occurred in database "BuildMart", table "dbo.Products", column 'Stock'.
The statement has been terminated.
Script finished

ProductId  Name              Stock
---------  ----------------  -----
        2  Cordless drill       10
        9  Wall plugs (100)      0

Most errors, like this constraint violation, end only the statement that failed. SQL Server then runs the next line as if nothing had happened. The drills were taken out of stock and the wall plugs were not: half a sale.

A transaction does not fix this by itself. BEGIN TRANSACTION … COMMIT groups the statements, but the failed statement does not cancel the transaction, so COMMIT happily saves the half that worked:

Query.sql
BEGIN TRANSACTION;    UPDATE dbo.Products SET Stock = Stock - 2 WHERE ProductId = 2;    UPDATE dbo.Products SET Stock = Stock - 5 WHERE ProductId = 9;COMMIT; SELECT ProductId, Name, Stock FROM dbo.Products WHERE ProductId IN (2, 9);
Output
Msg 547, Level 16, State 0, Line 3
The UPDATE statement conflicted with the CHECK constraint "CK_Products_Stock". The conflict occurred in database "BuildMart", table "dbo.Products", column 'Stock'.
The statement has been terminated.

ProductId  Name              Stock
---------  ----------------  -----
        2  Cordless drill        8
        9  Wall plugs (100)      0

TRY … CATCH

Put the statements in a BEGIN TRY block. When an error happens inside it, SQL Server jumps straight to the BEGIN CATCH block, and the rest of the TRY block is skipped. In CATCH you roll back and report:

Query.sql
BEGIN TRY    BEGIN TRANSACTION;        UPDATE dbo.Products SET Stock = Stock - 2 WHERE ProductId = 2;        UPDATE dbo.Products SET Stock = Stock - 5 WHERE ProductId = 9;    COMMIT;    PRINT N'Sale saved';END TRYBEGIN CATCH    IF @@TRANCOUNT > 0 ROLLBACK;    PRINT CONCAT(N'Sale cancelled: error ', ERROR_NUMBER(), N' on line ', ERROR_LINE());END CATCH; SELECT ProductId, Name, Stock FROM dbo.Products WHERE ProductId IN (2, 9);
Output
Sale cancelled: error 547 on line 4

ProductId  Name              Stock
---------  ----------------  -----
        2  Cordless drill        8
        9  Wall plugs (100)      0

The drill stock did not move this time: the first update was undone by the ROLLBACK. @@TRANCOUNT is the number of open transactions; checking it before ROLLBACK avoids a second error when the failure happened before BEGIN TRANSACTION.

Inside CATCH, a set of functions describes the error. Outside a CATCH block they return NULL.

Query.sql
BEGIN TRY    SELECT 10 / 0 AS Result;END TRYBEGIN CATCH    SELECT ERROR_NUMBER()   AS Number,           ERROR_SEVERITY() AS Severity,           ERROR_STATE()    AS State,           ERROR_LINE()     AS Line,           ERROR_MESSAGE()  AS Message;END CATCH;
Output
Result
------

Number  Severity  State  Line  Message
------  --------  -----  ----  ---------------------------------
  8134        16      1     2  Divide by zero error encountered.

The empty Result grid is real: the SELECT had started, and sent its column heading, before the division failed. Then the CATCH block ran and returned its own result. Errors are graded by severity:

SeverityMeaning
0–10information and warnings: not caught by CATCH, shown as messages
11–16errors the user can fix: constraint violations, bad data, THROW. Caught by CATCH
17–19resource or software problems, such as running out of space
20–25fatal: the connection is closed

Raising your own errors: THROW

THROW number, message, state; raises an error. Use numbers from 50000 up for your own errors. Inside CATCH, THROW; on its own re-raises the error that was caught, with its original number and message. That lets you clean up and still tell the caller what went wrong:

Query.sql
DECLARE @Qty int = 0; BEGIN TRY    IF @Qty <= 0        THROW 50001, N'Quantity must be at least 1.', 1;    PRINT N'Not reached';END TRYBEGIN CATCH    PRINT CONCAT(N'Logged: ', ERROR_MESSAGE());    THROW;   -- pass the same error on to the callerEND CATCH;
Output
Logged: Quantity must be at least 1.
Msg 50001, Level 16, State 1, Line 5
Quantity must be at least 1.

Older code uses RAISERROR. It can fill in values like printf, %s for text and %d for whole numbers, and its severity can be anything:

Query.sql
RAISERROR(N'Product %d has only %d in stock.', 16, 1, 9, 0);RAISERROR(N'Just a warning: severity 10 is not an error.', 10, 1);PRINT N'Still running';
Output
Msg 50000, Level 16, State 1, Line 1
Product 9 has only 0 in stock.
Just a warning: severity 10 is not an error.
Still running
`THROW``RAISERROR`
Re-raise the caught errorTHROW;not possible: you build a new error
Severityalways 16any; 10 or less is only a message
printf-style valuesno (use FORMATMESSAGE or CONCAT)yes: %s, %d
Error numberyour own, 50000 and upalways 50000 for a text message
Use in new codeyesfor warnings and progress messages

XACT_ABORT: make errors stop everything

SET XACT_ABORT ON changes the default. Any runtime error now ends the whole batch and rolls back the open transaction. A script with a transaction is all-or-nothing even without TRY … CATCH:

Query.sql
SET XACT_ABORT ON; BEGIN TRANSACTION;    UPDATE dbo.Products SET Stock = Stock - 2 WHERE ProductId = 2;    UPDATE dbo.Products SET Stock = Stock - 5 WHERE ProductId = 9;COMMIT;PRINT N'Not reached';GOSET XACT_ABORT OFF;SELECT ProductId, Name, Stock, @@TRANCOUNT AS OpenTransactionsFROM dbo.Products WHERE ProductId IN (2, 9);
Output
Msg 547, Level 16, State 0, Line 5
The UPDATE statement conflicted with the CHECK constraint "CK_Products_Stock". The conflict occurred in database "BuildMart", table "dbo.Products", column 'Stock'.

ProductId  Name              Stock  OpenTransactions
---------  ----------------  -----  ----------------
        2  Cordless drill        8                 0
        9  Wall plugs (100)      0                 0

With XACT_ABORT ON and TRY … CATCH, an error in the TRY block still jumps to CATCH, but the transaction is now doomed: it can only be rolled back. XACT_STATE() tells you: 1 means a healthy open transaction, 0 none, and -1 doomed. Trying to commit a doomed transaction is itself an error:

Query.sql
SET XACT_ABORT ON; BEGIN TRY    BEGIN TRANSACTION;        UPDATE dbo.Products SET Stock = Stock - 5 WHERE ProductId = 9;    COMMIT;END TRYBEGIN CATCH    PRINT CONCAT(N'XACT_STATE() = ', XACT_STATE());    COMMIT;   -- wrong: a doomed transaction can only be rolled backEND CATCH;GOSET XACT_ABORT OFF;IF @@TRANCOUNT > 0 ROLLBACK;
Output
XACT_STATE() = -1
Msg 3930, Level 16, State 1, Line 10
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Interactive

Does the sale survive the error?

BEGIN TRY
    BEGIN TRANSACTION;
        UPDATE dbo.Products SET Stock = Stock - 2 WHERE ProductId = 2;
        UPDATE dbo.Products SET Stock = Stock - 5 WHERE ProductId = 9;
    COMMIT;
    PRINT N'Committed';
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    PRINT CONCAT(N'Caught error ', ERROR_NUMBER());
END CATCH;
GO
SELECT ProductId, Name, Stock FROM dbo.Products
WHERE ProductId IN (2, 9) ORDER BY ProductId;
SELECT @@TRANCOUNT AS OpenTransactions;
What SSMS shows
Caught error 547
ProductIdNameStock
12Cordless drill12
29Wall plugs (100)0
OpenTransactions
10
The error jumped to CATCH, which rolled back. Nothing was saved: the drill stock is still 12.

Errors CATCH cannot see

TRY … CATCH only catches errors that happen while the TRY block runs. An error found while the batch is being compiled, such as a syntax error, stops the batch before TRY starts. A table that does not exist is a trap: the name is checked only when that statement is about to run, and that error is not caught at the same level either:

Query.sql
BEGIN TRY    SELECT * FROM dbo.NoSuchTable;END TRYBEGIN CATCH    PRINT N'Caught it';END CATCH;
Output
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.NoSuchTable'.

The same error is caught when it happens one level down, inside a procedure or dynamic SQL called from the TRY block:

Query.sql
BEGIN TRY    EXEC sp_executesql N'SELECT * FROM dbo.NoSuchTable';END TRYBEGIN CATCH    PRINT CONCAT(N'Caught ', ERROR_NUMBER(), N': ', ERROR_MESSAGE());END CATCH;
Output
Caught 208: Invalid object name 'dbo.NoSuchTable'.

A procedure template

Put it all together and most data-changing procedures can follow one shape: XACT_ABORT ON, one transaction inside TRY, and a CATCH that rolls back and re-throws so the caller hears about it.

Query.sql
CREATE OR ALTER PROCEDURE dbo.SellProduct    @ProductId int,    @Qty       intASBEGIN    SET NOCOUNT ON;    SET XACT_ABORT ON;     BEGIN TRY        IF @Qty <= 0            THROW 50001, N'Quantity must be at least 1.', 1;         BEGIN TRANSACTION;            UPDATE dbo.Products SET Stock = Stock - @Qty WHERE ProductId = @ProductId;            IF @@ROWCOUNT = 0                THROW 50002, N'No such product.', 1;        COMMIT;    END TRY    BEGIN CATCH        IF @@TRANCOUNT > 0 ROLLBACK;        THROW;    END CATCH;END;GOEXEC dbo.SellProduct @ProductId = 3, @Qty = 5;SELECT Name, Stock FROM dbo.Products WHERE ProductId = 3;EXEC dbo.SellProduct @ProductId = 9, @Qty = 1;
Output
Name              Stock
----------------  -----
Tape measure 8 m     60

Msg 547, Level 16, State 0, Procedure dbo.SellProduct, Line 14
The UPDATE statement conflicted with the CHECK constraint "CK_Products_Stock". The conflict occurred in database "BuildMart", table "dbo.Products", column 'Stock'.
Quick check

Without TRY … CATCH or XACT_ABORT, the second of three statements inside BEGIN TRANSACTION … COMMIT fails with a constraint violation. What is saved?

Quick check

With XACT_ABORT ON, an error in a TRY block jumps to CATCH. What does XACT_STATE() return there?

Practice

Write a script that, in one transaction, adds 10 to the stock of product 1 and takes 999 from the stock of product 3 (which breaks the CHECK constraint). Use TRY … CATCH so that neither change is kept, and print the error message in CATCH. Finish with a SELECT of both products’ stock.

Show a hint

BEGIN TRY, BEGIN TRANSACTION, the two UPDATEs, COMMIT, END TRY; then BEGIN CATCH with IF @@TRANCOUNT > 0 ROLLBACK and PRINT ERROR_MESSAGE().

Show the solution
Solution.sql
BEGIN TRY    BEGIN TRANSACTION;        UPDATE dbo.Products SET Stock = Stock + 10  WHERE ProductId = 1;        UPDATE dbo.Products SET Stock = Stock - 999 WHERE ProductId = 3;    COMMIT;END TRYBEGIN CATCH    IF @@TRANCOUNT > 0 ROLLBACK;    PRINT ERROR_MESSAGE();END CATCH; SELECT ProductId, Name, Stock FROM dbo.Products WHERE ProductId IN (1, 3);
Output
The UPDATE statement conflicted with the CHECK constraint "CK_Products_Stock". The conflict occurred in database "BuildMart", table "dbo.Products", column 'Stock'.

ProductId  Name              Stock
---------  ----------------  -----
        1  Claw hammer          40
        3  Tape measure 8 m     60

Remember

  • By default most errors end only the failing statement; the batch and the transaction carry on.
  • BEGIN TRANSACTION alone is not all-or-nothing: COMMIT saves whatever succeeded.
  • TRY … CATCH jumps to CATCH on an error; roll back there with IF @@TRANCOUNT > 0 ROLLBACK.
  • ERROR_NUMBER, ERROR_MESSAGE, ERROR_LINE, ERROR_PROCEDURE … describe the error inside CATCH.
  • THROW n, msg, state raises your own error (50000+); THROW; on its own re-raises the caught one.
  • SET XACT_ABORT ON makes any error abort the batch and roll back; in CATCH the transaction is doomed (XACT_STATE() = -1).
  • Compile errors and missing objects in the same scope are not caught; they are one level down.
  • Template: SET XACT_ABORT ON; TRY { BEGIN TRAN … COMMIT } CATCH { IF @@TRANCOUNT > 0 ROLLBACK; THROW; }.
0 of 54 lessons completed