SQL Server MVP Erland Sommarskog has written a staggeringly detailed and thorough series of articles on error handling in T-SQL.
In the first of these articles Erland presents a pattern for error handling that he suggests is suitable for 90-95% of coding situations, and that works for SQL Server 2005-2016. I’m implementing it here, and also discussing the reasoning behind key parts of the pattern’s design and construction.
The key point to understand about this pattern is that it’s a design for handling unanticipated errors – it’s not aimed at dealing with errors that are naturally part of an application’s business logic, such as checking that input data meets certain validation rules.
The pattern is as follows – used here within a stored procedure and wrapped around a simple piece of business logic:
I discuss each part of it in the sections below.
When XACT_ABORT ON is set and a T-SQL statement raises a run-time error, in the vast majority of cases any transaction in progress is terminated and rolled back. This setting is used to provide greater consistency in how SQL Server behaves in error situations, thus allowing for a simpler and more effective generic error handler. Without it, the transaction may not be terminated, and it may not roll back, i.e. subject to the particular type of error that occurred.
(This behaviour isn’t universally true though – one notable exception is errors generated by RAISERROR, which do not terminate or rollback even with XACT_ABORT ON. They do get transferred to the CATCH block of a TRY…CATCH handler though, and with XACT_ABORT ON, any transaction is doomed and so can only be rolled back and not committed.)
An example where a transaction neither terminates nor rolls back when an error occurs unless XACT_ABORT is ON is if an explicit transaction contains a sequence of inserts to a table that references another table via a foreign key constraint, and where one of the inserts violates that constraint. If this is not within a TRY block, then the transaction’s COMMIT will commit all the inserts that succeeded and ignore the one that failed, and if it is within a TRY block then any COMMIT used in the CATCH block will commit any database changes up to the point of the exception.
Erland provides the following table to enumerate the (confusing!) array of options with regards the different types of error that can occur and what their behaviour is relative to whether a TRY…CATCH construct is used and whether XACT_ABORT is ON or OFF:
So the foreign key violation case that I just described is an example of a Class 4 error above, where it can be seen to be only statement-terminating with no rollback if executed with no TRY…CATCH construct and with XACT_ABORT OFF, and where it raises a catchable exception if executed within a TRY…CATCH and with XACT_ABORT ON, and where the transaction in progress must be rolled back (because it is doomed).
It can be seen from the right side of the table that using a TRY…CATCH construct in conjunction with SET XACT_ABORT ON does provide for the greatest consistency of behaviour.
The TRY…CATCH construct has existed since SQL Server 2005 and allows error logic to be concentrated within the CATCH block rather than be peppered throughout the business logic (as was required in earlier versions if @@error was checked after every statement that could fail).
Specifically it catches error messages with severity between 11 and 19 – messages below 11 are considered informational rather than being actual errors, and messages >= 20 do signify errors but they terminate the connection to the database and so cannot be handled.
Note that the SET statement for XACT_ABORT (and NOCOUNT) is the only statement that should appear outside the outermost TRY…CATCH construct of a procedure in this pattern. (It could actually be within the TRY block, but Erland suggests having outside because it’s ‘noise’ that has nothing to do with the core business logic of the TRY block.)
As Erland says in his articles, in a database application, error handling is also about transaction handling, i.e. in the context of an error we want a transaction to be atomic and to be rolled back. This means that after an unanticipated error, the database is not left logically inconsistent, and we’ve returned to a known, clean slate.
So in the pattern above the TRY block uses BEGIN TRANSACTION / COMMIT TRANSACTION to encapsulate the business logic, which together with the XACT_ABORT ON behaviour in the context of TRY…CATCH gives us reasonably consistent behaviour over a range of error conditions. A transaction doesn’t have to appear within the TRY block though, e.g. if the stored procedure is always called within the context of an outer transaction or if there’s only a single statement within the TRY block that updates the database.
Also unlike the minimal example above, sometimes there may be code between COMMIT TRANSACTION and END TRY, e.g. a final SELECT to return data or a SET or SELECT statement to assign values to output parameters.
Whereas the TRY block will look different from procedure to procedure, the CATCH block will always remain the same (at least for the situations where this pattern applies). The CATCH block contains these 3 actions:
- Rollback the transaction
- Reraise the error (via a utility stored procedure in this case)
- Return a non-zero status value
As mentioned in the previous section, we need the ROLLBACK to return us to a clean slate, since an error has occurred outside the scope of errors we were expecting. It’s possible no explicit transaction was started in the TRY block (e.g. in the case of just 1 update statement), but the condition guarding the ROLLBACK allows for this, and having the statement there from the outset avoids forgetting to put it in if ever an explicit transaction were added.
SQL Server has the notion of a doomed transaction, i.e. a transaction that’s in a state where it must be rolled back. Setting XACT_ABORT to ON always dooms the transaction in the case of an error that gets transferred to the CATCH block, so there’s never any choice about whether to COMMIT or ROLLBACK the transaction – it must be rolled back.
It’s possible that a transaction was started outside the procedure where our error handler is activated, in which case we’re possibly rolling back more than what we altered in the TRY block. Erland considers this here, looking in particular at savepoints to see whether they can be used to roll back only the procedure’s own work and no other. His well reasoned conclusion is that savepoints cannot help, and that the only safe option is to rollback all modifications that occurred since the beginning of the transaction.
After rolling back, the second action of the CATCH block re-raises the error. A utility stored procedure (discussed in the next section) is used to do this in my example, but Erland also suggests 2 other possibilities, which are: (i) using THROW (without parameters), and (ii) using a framework that Erland wrote called SqlEventLog, which includes functionality such as logging the unanticipated error to an event log table.
The error_handler_sp stored procedure is Erland’s general recommendation though, not least because it works on all versions of SQL Server from SQL 2005 and up. (THROW without parameters for the re-raise is certainly less code though, and will still be an option for 2012 or later installations. A risk of using it however is forgetting to precede it with a semi-colon, and not picking this up during testing. In this case when the error handler runs for real, the missing semi-colon results in the error message “Cannot roll back THROW. No transaction or savepoint of that name was found.”, instead of the original error that occurred.)
The third action of the catch block – the RETURN statement – is a safeguard. The RAISERROR that occurs within error_handler_sp doesn’t abort execution, so execution will continue with the next statement. As long as all procedures are using TRY…CATCH and additionally all client code is using exception handling then the return value will generally be of less importance. But the return value may well be interrogated if the procedure is called from legacy T-SQL code that was written before the introduction of TRY…CATCH, or if the stored procedure is called by an external application that doesn’t have exception-based error handling.
The CATCH block in the example above re-raises the error via a stored procedure that Erland wrote for his pattern called error_handler_sp. Use of this stored procedure avoids code duplication within CATCH blocks and ensures simplicity and consistency throughout them. It first calls system functions to gather information about the error, and then feeds this into a RAISERROR statement.
It can be seen that the error is always re-raised – this again relates to the fact that we’re dealing with unanticipated errors in the outer catch block of a stored procedure with this pattern, and this type of error should almost always be communicated back to the caller. On occasion a retry of the failed logic by the caller would make sense, e.g. if the cause of the error was a deadlock.
Note that Erland is using RAISERROR here rather than THROW – he compares them in his article here and comes to the conclusion that: “Given that ;THROW with parameters is less versatile than RAISERROR without any particular simplicity in syntax, there is little reason why you would use it. This applies even more if you have an existing system where you already use RAISERROR, since the subtle differences between the commands is bound to cause confusion.”
An example of an error raised by this stored procedure is:
So although RAISERROR references error_handler_sp as being the source of the error, the message passed to it contains the actual procedure name where the error occurred.
Erland makes a good justification in his articles for the above pattern being effective for most stored procedures (see here for his comments with regards error handling in UDFs and triggers) and across a wide range of SQL Server versions.
As stated at the beginning, this pattern is focused upon handling unanticipated errors – this passage from Erland describes well his motivation here:
“An unanticipated error is just that: an error that occurred in a piece of code where you did not expect an error to occur. The error could be due to a bug in your code, incorrect data from a caller you had reason to trust, or it could also be a concurrency issue like a deadlock. And you should not overlook the possibility that someone three years from now makes a change to a table that breaks your code. For this reason, you need to have a general preparedness for that things could go wrong. Once you have a framework for unanticipated errors, you should be able to extend it to handle anticipated errors in the situations where you need to do this.”
A final point to make is that all error handling must be tested as far as is practical. It’s easy to miss paths through code that invoke error handlers, but even with a boiler-plate approach such as this where its simplicity, consistency and reuse of a utility stored procedure help to minimise mistakes, error handlers need to be provoked in order to verify their behaviour.