TRY CATCH THROW: Error handling changes in T-SQL
November 22nd, 2010When SQL Server 2005 introduced BEGIN TRY and BEGIN CATCH syntax, it was a huge improvement over the previous error handling based on @@ERROR check after each statement. Finally, T-SQL joined the rank of programming languages, no more just a data access language. Experience has shown that exception handling leads to better code compared to error checks. Yes, SEH is slower, but is basically impossible to maintain the code discipline to check @@ERROR after every operation, so exception handling is just so much easier to get right. And besides, @@ERROR never had such a masterpiece article to guide you trough like A Crash Course on the Depths of Win32™ Structured Exception Handling.
But when trying to use the new TRY/CATCH exception handling in T-SQL code, one problem quickly became apparent: the CATCH block was masking the original error metadata: error number/severity/state, error text, origin line and so on. Within a CATCH block the code was only allowed to raise a *new* error. Sure, the original error information could be passed on in the raised error message, but only as a message. The all important error code was changed. This may seem like a minor issue, but turns out to have a quite serious cascading effect: the caller now has to understand the new error codes raised by your code, instead of the original system error codes. If the application code was prepared to handle deadlocks (error code 1205) in a certain way (eg. retry the transaction), with a T-SQL TRY/CATCH block the deadlock error code would all of the sudden translate into something above 50000.
With SQL Server 11, this is not the case anymore. THROW was introduced in the language to allow the exception handling to re-throw the original error information. Revisiting the stored procedure template I recommended to use for proper handling of nested transactions in the presence of exception in Exception handling and nested transactions, here is how the template would be modified for SQL Server 11 to take advantage of THROW:
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do the actual work here
lbexit:
if @trancount = 0
commit;
end try
begin catch
declare @error int, @message varchar(4000), @xstate int;
select @error = ERROR_NUMBER()
, @message = ERROR_MESSAGE()
, @xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback
if @xstate = 1 and @trancount > 0
rollback transaction usp_my_procedure_name;
throw;
end catch
end
RAISERROR is deprecated
11/23: I leave this text here as I originally wrote it, but read bellow why almost everything I say here is wrong.
With the introduction of THROW, RAISERROR was declared obsolete and put on the future deprecation list. THROW can be used instead of RAISERROR to throw a new error:
THROW 51000, 'The record does not exist.', 1;
New exceptions raised with THROW will all have a severity level 16. Needless to say, exception re-thrown from a CATCH block preserve the original severity. THROW without additional arguments can only be used inside a CATCH block. THROW with explicit error number can be used in any place in code.
But RAISERROR had a very handy feature: it could format the error message and replace, printf style, arguments into it. And since severity 0 was basically a PRINT, it was a very handy replacement for the cumbersome and archaic PRINT restriction (remember, PRINT can only print one and only one variable/message per line). But THROW does not allow for argument replacement in the message. Instead, the guidance is to use the FORMATMESSAGE infrastructure. Is true that FORMATMESSAGE has localization support, but that will hardly sugar coat the sorrow pill of taking away message formatting like RAISERROR had:
- Application developers have to deal with localization in the application front end anyway, so they much rather deal with one uniform localization infrastructure, and that one infrastructure must be the one that support the front end features: resource DLLs.
- Database errors do not make it to the localized front end. Displaying errors about allocation failures due to file growth restrictions or page checksum validation errors are hardly of any value to the end user, and are very often disclosing information that was not supposed to leak to the front-end. Most applications make use of the database errors solely for logging, which is not localized in the end-user language but instead must be understood by the developers.
- Lacking support for constants in T-SQL makes development of code that uses magic numbers problematic. FORMATMESSAGE (52113, ...) what the heck is 52133? I rather have FORMATMESSAGE(ERROR_RECORD_MISSING,...)...
- Message IDs have no namespace. As global values in the database, the danger of conflicts between side-by-side deployed applications is always present.
- Message IDs have to be provisioned at application deployment time. With the deployment/setup/upgrade story for T-SQL being already in a pretty bad shape, no sane developer would add another dependency on that.
Given these points, is no wonder that message ID based errors are basically unheard of in the T-SQL backed application development. I feel that the FORMATMESSAGE story as a replacement for deprecation of the RAISERROR formatting capabilities is a step backward for the new THROW syntax.
Update 11/23
As Aaron pointed out, the MSDN quote about RAISERROR is a documentation error. The function is not deprecated. Furthermore the FORMATMESSAGE function was actually enhanced to support ad-hoc formatting:
SELECT FORMATMESSAGE('Hello %s!', 'World');
Between these two additional pieces of information, my rant concern about the deprecation of RAISERROR and the lack of ad-hoc formatting in FORMATMESSAGE is obsolete and... deprecated.
RAISERROR is *not* deprecated, this is an error in Books Online. Only the old style of RAISERROR is deprecated (and has been since 2008):
RAISERROR 66666 ‘some text’;
[…] This post was mentioned on Twitter by Remus Rusanu, Niels Berglund. Niels Berglund said: THROW in #denali by @rusanu http://bit.ly/cIMDaT & @AaronBertrand http://bit.ly/cKmic7. This in addition to my post http://bit.ly/9JrUam […]