Changes To Exception Handling In SQL Server 2011

When SQL Server 2005 was released, we saw the introduction of structured exception handling using BEGIN TRY, BEGIN CATCH statements. This fantastic feature allowed us to trap and handle exceptions that we may encounter in the course of our code execution, in the same way that you can handle exceptions in .Net applications . This was fantastic news for DBAs and developers because it meant that we were able write code to cope with certain exceptions in much more efficient manner – a vast improvement over the ‘IF @@ERROR <> 0 do something’ statements.

However, there was a catch. Once inside the catch block, if you encountered an exception that your code was not designed to handle; it was not possible to pass the original exception back to the caller without mucking about with the message string. This meant that any application that used the exception information to determine if it could handle the exception gracefully, had to read the message string of the exception to find the information it needed to decide what to do; or you used the return value of the stored procedure to indicate the error. Either way, not a pretty sight; and certainly not very efficient!

With the advent of SQL Server 2011, it looks like all that may change! The first thing that you should note is that the RAISERROR function has now been depreciated.. the replacement is something that all dot Net developers should be familiar with – the THROW statement.

The THROW statement has been implemented with 2 goals in mind, the first is that you can THROW your own errors (anything with an error number over 50,000 – same as RAISERROR) by supplying the THROW statement with a set of parameters: –

THROW error_number or @variable, message or @variable, state or @variable

• error_number or @variable is any number between 50,000 and 2147483647 (INT).
• message or @variable supports 2047 characters of the NVARCHAR data type – it does not support printf formatting, but there is a new formatting function you can use called FORMATMESSAGE.
• state or @variable is any number between 0 and 255 (TINYINT).

As you can see, it’s not that different to using the RAISERROR statement, with one notable difference – you can no longer supply the severity as a parameter. The severity is always 16.

Passing your custom error to the caller: –

BEGIN TRY
	--custom error
	THROW 50000,N'My custom exception.',1;

END TRY
BEGIN CATCH
	PRINT 'In CATCH block. Passing my custom exception to the caller..';
	THROW;

END CATCH

The second way to use the THROW statement is without supplying any parameters at all.

BEGIN TRY
	--Divide by 0 error
	SELECT 1/0

END TRY
BEGIN CATCH
	PRINT 'In CATCH block. Pass the exception to the caller.';
	THROW

END CATCH

This allows you pass the original exception back to the caller, allowing the caller to decide if it needs the user to do anything or if the exception can be handled gracefully in the application itself, without any of complications of searching message strings etc.

It’s a feature that’s been lacking for a long time, but now that it looks like it’s finally here.. thank you Microsoft! Hopefully, this feature will remain when the product is released to manufacturing; and if is, I suggest you start using the feature as soon as possible.