One of the most interesting error handling enhancements added in SQL Server 2005 is the support for structured exception handling using TRY / CATCH blocks. This reduced the complexity of error handling in TSQL significantly.

Exception Handling using TRY/CATCH

Exception handling using TRY/CATCH is very much similar to how it is done in other programming languages. You put your code between a BEGIN TRY and END TRY block and write the error handling code within the BEGIN CATCH and END CATCH block. Here is a very basic example that shows structured exception handling in TSQL using TRY/CATCH.

BEGIN TRY
	SELECT 1/0
END TRY
BEGIN CATCH
	PRINT 'Error: ' + ERROR_MESSAGE()
	PRINT 'Number: ' + CAST(ERROR_NUMBER() AS VARCHAR)
END CATCH
/*
Error: Divide by zero error encountered.
Number: 8134
*/

Throwing a Database Error to the Application

Most serious applications handle data access errors in the application as well as in the database. Database developers will put their code within TRY/CATCH blocks and log the error details into a table for offline analysis.

Some of the database errors may be relevant to the application developers as well. For example, if an error is raised due to an incorrect value being passed into the stored procedure, the application developers need to know about it. In such a case, the database developer might need to throw the error back the application after doing the database level error logging.

Here is an example that shows how to do this.

BEGIN TRY
	SELECT 1/0
END TRY
BEGIN CATCH
	DECLARE @errn INT = ERROR_NUMBER()
	DECLARE @errm VARCHAR(500) = ERROR_MESSAGE()
	RAISERROR('Error Number: %d, Message: %s', 16, 1, @errn, @errm)
END CATCH
/*
Msg 50000, Level 16, State 1, Line 7
Error Number: 8134, Message: Divide by zero error encountered.
*/

Problems with RAISERROR

RAISERROR, like any other function/command has got its own set of capabilities and shortcomings. One relatively small problem I found with RAISERROR is the inability to RAISE the same error after catching it in a CATCH block. In the above example, note that the error caught in the CATCH block was 8134. There is no way to RE-RAISE the same error using RAISERROR. If you attempt to raise an error with Error Number 8134 (or other system error numbers), you will get an error as shown in the following example.

BEGIN TRY
	SELECT 1/0
END TRY
BEGIN CATCH
	DECLARE @errn INT = ERROR_NUMBER()
	DECLARE @errm VARCHAR(500) = ERROR_MESSAGE()
	RAISERROR(8134, 16, 1)
END CATCH
/*
Msg 2732, Level 16, State 1, Line 7
Error number 8134 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000.
*/

Another problem is that when re-raising an error using RAISERROR, you loose the original error line number. In the example that we saw in the previous section, the actual error occurred on line 2, but when RAISERROR is called, the error line is reset to the current line number (7).

Enter THROW

THROW is a new command introduced in SQL11 (Denali) and it helps you to overcome the limitations we saw above. After performing your error handling within the CATCH block, you can use the THROW command to re-throw the same error to the calling procedure/application.

BEGIN TRY
	SELECT 1/0
END TRY
BEGIN CATCH
	PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR)
	PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR)
	PRINT 'Throwing error...';
	THROW
END CATCH
/*
Error Number: 8134
Error Line: 2
Throwing error...

Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
*/

Note that the same error number and line number is propagated when using the THROW command. It is important to note that the THROW command should be issued as a new batch and therefore you need to terminate the previous batch by placing a semi colon before THROW.