As of SQL Server 2005, you can handle errors using a TRY-CATCH block, similar to “real” programming languages. This enables you to trap most common errors and handle them, instead of having your entire batch or procedure fail with an error message.
How TRY-CATCH works
A TRY-CATCH statement consists of two parts: The first part is TRY block, which is the code you actually want to run. The second part is the CATCH block, which contains the code to handle if the TRY block didn’t work out.
Here’s a simple example:
BEGIN TRY;
--- This will fail because of a foreign key constraint.
DELETE FROM Person.AddressType;
END TRY
BEGIN CATCH;
PRINT 'Well, that didn''t pan out.';
END CATCH;
If the code in the TRY block runs without any errors, SQL Server will simply pass over the CATCH block and continue executing after it. If, however, the code in the TRY block fails, SQL Server will execute the code in the CATCH block.
Errors will always happen, and it’s good practice to trap and manage them. It can be a matter of foreign key constraints, invalid datatype conversions, duplicate key errors, division by zero or any number of other problems that are near-impossible to anticipate when you’re developing a solution. Using TRY-CATCH blocks can make the difference between an entire nightly batch job crashing and burning, or gracefully handling the error and completing what can be done.
Related system functions
Once we’ve trapped an error, you may want to write it to a log table, e-mail it or print it. There are a number of functions that let you access the most recent error message as well as what line in the batch it occurred on, etc.
ERROR_NUMBER()
Returns the “number” of the error, the same number that you see when you see an error message that start with “Msg nnn, line …”. This number doesn’t change with different language settings, so it can be pretty useful if you want to check for a specific type of error without worrying about the user’s locale.
ERROR_SEVERITY()
This is the severity of the error. Generally, levels up to 10 are informational messages or warnings, 11 through 16 are user-correctable errors and errors above 16 are critical errors, such as resource problems, integrity errors or even hardware errors.
ERROR_STATE()
State codes are give you a more specific origin of the error. The same error can occur for different reasons, and the error state can give you a further clue in this respect.
ERROR_PROCEDURE()
Returns the procedure (a stored procedure, trigger, etc) where the error was raised. This can be very useful when you’re nesting procedures.
ERROR_LINE()
The line number in the batch or procedure where the error was raised.
ERROR_MESSAGE()
This is the plaintext error message.
Using XACT_ABORT
A flag that can affect error handling is XACT_ABORT. This flag determines if a transaction is aborted if there is an error along the way. In the following example code, we’re running a transaction with three inserts, the second of which creates a duplicate key violation:
CREATE TABLE #test (
a int NOT NULL,
PRIMARY KEY CLUSTERED (a)
)
SET XACT_ABORT ON;
BEGIN TRANSACTION;
INSERT INTO #test (a) VALUES (1), (2), (3);
INSERT INTO #test (a) VALUES (3); --- Duplicate row!
INSERT INTO #test (a) VALUES (4), (5), (6);
COMMIT TRANSACTION;
SELECT * FROM #test;
DROP TABLE #test;
This query will crash on the second INSERT with the following error message:
Msg 2627, Level 14, State 1, Line 11 Violation of PRIMARY KEY constraint 'PK__#test'. Cannot insert duplicate key in object 'dbo.#test'. The duplicate key value is (3).
If you were to change the setting of XACT_ABORT to OFF, the second INSERT statement will still fail, and it will still return the same error message, but SQL Server will move on to execute the third INSERT, commit the transaction and SELECT the output.
Msg 2627, Level 14, State 1, Line 11 Violation of PRIMARY KEY constraint 'PK__#test'. Cannot insert duplicate key in object 'dbo.#test'. The duplicate key value is (3). The statement has been terminated. a ----------- 1 2 3 4 5 6
The default setting of XACT_ABORT is ON. Turning it OFF can lead to some very confusing debugging, so I would strongly recommend always leaving it ON.
Nesting TRY-CATCH blocks
Nesting TRY-CATCH blocks is perfectly fine, just as you would want to nest SQL transactions.
However, when you catch an error using a TRY-CATCH block, you’ll have to remember that the current procedure or context won’t raise the error to the calling procedure. This is by design – we don’t want the batch to crash, right?
Example: Procedure A calls procedure B, which calls procedure C. If there is an (unhandled) error in procedure C, this error will crash procedure C, which will crash B and A as well. However, if we handle (catch) the error in procedure C, everything will look allright to procedure B. Likewise, you could put the TRY-CATCH block in procedure B and handle the error there. Or both.
The important part is to remember that if you want to “re-throw” an error, you’ll have to do this manually in the CATCH block, using for instance RAISERROR().
XACT_STATE() and uncommittable transactions
If you catch an error in a SQL transaction, chances are that the error has caused the transaction to become “doomed”. A doomed transaction cannot be continued or committed and is read-only until you perform a rollback. When you try to write something to the transaction log within a doomed transaction, you will get the following error message:
Msg 3930, Level 16, State 1, Line 31 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Generally speaking, uncommittable transactions (as they’re formally known) happen when you CATCH an error with severity level 16 or higher, in combination with having XACT_ABORT ON. I say “generally speaking”, because there appears to be a number of conditions that define when a transaction can or cannot commit.
You can check if your transaction is committable using the XACT_STATE() function. If it is -1, the transaction is doomed and cannot be committed. 0 means that there is no current SQL transaction at all. 1 means that the transaction can still be committed.
Here’s an example of some code that will cause an uncommittable transaction in a CATCH block:
CREATE TABLE #test (
a int NOT NULL,
PRIMARY KEY CLUSTERED (a)
)
SET XACT_ABORT ON;
BEGIN TRY;
BEGIN TRANSACTION;
INSERT INTO #test (a) VALUES (1), (2), (3);
INSERT INTO #test (a) VALUES (3); --- Duplicate
INSERT INTO #test (a) VALUES (4), (5), (6);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH;
PRINT ERROR_MESSAGE();
PRINT (CASE XACT_STATE()
WHEN -1 THEN 'Uncommittable'
WHEN 0 THEN 'No transaction'
WHEN 1 THEN 'Committable' END);
--- If committable, commit.
IF (XACT_STATE()=1)
COMMIT TRANSACTION;
--- If not committable, roll back.
IF (XACT_STATE()=-1)
ROLLBACK TRANSACTION;
END CATCH;
SELECT * FROM #test;
DROP TABLE #test;
Because XACT_ABORT in the example above is ON, the entire transaction is rendered uncommittable by the duplicate key violation. The ROLLBACK statement results in the temp table remaining empty at the end of the batch.
If you re-run the batch with XACT_ABORT OFF, you will still catch the duplicate key error, but the transaction will be committable. This will commit the table with the first three rows from the first INSERT statement.Interestingly, the third INSERT statement will never run within a TRY-CATCH block, no matter if XACT_ABORT is ON or OFF. Without the TRY-CATCH block, however, the third INSERT statement will execute if XACT_ABORT is set to OFF, as we saw earlier in this post.
Let me know if you find this article interesting, and remember to “like” the Facebook page, to make sure you get an update on your feed whenever there’s a new post. Have a nice week!