Error Handling
Prior to the introduction of SQL Server 2005, error handling in Transact-SQL (T-SQL) scripts was very restrictive. If error handling was included in a batch or stored procedure, it would usually be limited to reading the value of @@ERROR immediately after attempting to execute a statement. If @@ERROR was zero, all was probably well. However, this approach was far from ideal, particularly as the value would be cleared after every executed statement.
Newer versions of SQL Server have improved error handling with the addition of the try / catch block, which is similar in operation to the try / catch block provided by C# and other languages. This code structure can be used in batches, stored procedures, triggers and other areas. It allows one or more statements to be executed within a try block. If an error occurs during processing, the block is exited immediately and control is passed to the catch block, where you can include statements that allow graceful recovery or report the error and leave the system in a stable state.
Try / Catch Blocks
The try / catch structure contains two blocks of statements. These are the statements that are to be attempted and those that will execute if an error is raised. The syntax for these blocks is as follows:
BEGIN TRY
[statements to try]
END TRY
BEGIN CATCH
[statements to run on error]
END CATCHCapturing Errors
The use of the try / catch block can be made more obvious with an example. In the script below the statement within the try block is attempting to divide one by zero before outputting a success message. This will always cause an error because the result of such a division cannot be represented in T-SQL. The catch block outputs a message if an error occurs.
When you execute this script the division is attempted and fails. Without the try / catch structure this would cause the script to stop executing immediately. As the division occurs within a try block, control is passed to the catch block instead. The result is that the success message is not shown but the error detection message is. NB: If you run this script within SQL Server Management Studio with default settings, the message is shown in the Messages tab of the results.
BEGIN TRY
SELECT 1 / 0
PRINT 'Success'
END TRY
BEGIN CATCH
PRINT 'Error Detected'
END CATCH
/* OUTPUT
Error Detected
*/If no errors occur whilst processing a try block, control passes to the statement immediately after the catch block. Similarly, if no errors occur within a catch block, processing continues with the statement that follows it. This means that whether or not the try block is interrupted by an error, the execution of the entire script will continue. Remember though that an error within the catch block will not be captured.
We can see this by adding an extra message after the entire try / catch structure. In the script below, the error message and the continuation are displayed. If you remove the division statement the success and continuation text will be outputted.
BEGIN TRY
SELECT 1 / 0
END TRY
BEGIN CATCH
PRINT 'Error Detected'
END CATCH
PRINT 'Continued OK'
/* OUTPUT
Error Detected
Continued OK
*/Nesting Try / Catch Blocks
If the error handling requirements of your scripts are more complex, you can nest try / catch blocks. It is possible to include a try / catch within either or both of the try and catch blocks of an outer structure. In either case, an error raised in an inner try will not be captured by the outer catch. This is demonstrated in the script below. In this case the division fails in the inner try. The message defined in the inner catch is outputted but the text from the outer catch is not.
BEGIN TRY
BEGIN TRY
SELECT 1 / 0
END TRY
BEGIN CATCH
PRINT 'Error Detected in Inner TRY'
END CATCH
END TRY
BEGIN CATCH
PRINT 'Error Detected in Outer TRY'
END CATCH
PRINT 'Continued OK'
/* OUTPUT
Error Detected in Inner TRY
Continued OK
*/3 October 2010