SQL Server 2005 offers major improvements in error
handling inside T-SQL transactions. You can now catch T-SQL and
transaction abort errors using the TRY/CATCH model without any loss of the transaction context. The only types of errors that the TRY/CATCH
construct can’t handle are those that cause the termination of your
session (usually errors with severity 21 and above, such as hardware
errors). The syntax is shown here:
BEGIN TRY
sql statement
END TRY
BEGIN CATCH TRAN_ABORT
--sql statement for catching your errors
END CATCH
If an error within an explicit transaction occurs inside a TRY block, control is passed to the CATCH block that immediately follows. If no error occurs, the CATCH block is completely skipped.
You can investigate the type of error that was raised and react accordingly. To do so, you can use the new ERROR functions to return error information in the CATCH block, as shown here:
Listing 1. T-SQL exception handling example
BEGIN TRY
SELECT 5/0
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
|
You can examine the value of the ERROR
functions to decide what to do with the control flow of your procedure
and whether to abort any transactions. When you experience a
transaction abort error inside a transaction located in the TRY block, control is passed to the CATCH
block. The transaction then enters a failed state in which locks are
not released and persisted work is not reversed until you explicitly
issue a ROLLBACK
statement. You’re not allowed to initiate any activity that requires
opening an implicit or explicit transaction until you issue a ROLLBACK.
Sometimes certain types of errors are not detected by the TRY/CATCH block, and you end up with an unhandled exception even though the error occurred inside your TRY block. If this happens, the CATCH block is not executed. This is because CATCH
blocks are invoked by errors that take place in actual executing code,
not by compile or syntax errors. Two examples of such errors are syntax
errors and statement-level recompile errors (for example, selecting
from a nonexistent table). These errors are not caught at the same
execution level as the TRY block, but at the lower level of execution—when you execute dynamic SQL or when you call a stored procedure from the TRY block. For example, if you have a syntax error inside a TRY block, you get a compile error and your CATCH block will not run:
-- Syntax error doesn't get caught
BEGIN TRY
SELECT * * FROM XYX
END TRY
BEGIN CATCH
PRINT 'Error'
END CATCH
GO
The result is an error from SQL Server, not from your CATCH block:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '*'.
Statement-level recompilation errors don’t get caught by CATCH blocks, either. For example, using a nonexistent object in a SELECT statement in the TRY block forces an error from SQL Server, but your CATCH block will not execute:
-- Statement level recompilation doesn't get caught
-- Example - nonexistent object
BEGIN TRY
SELECT * FROM XYX
END TRY
BEGIN CATCH
PRINT 'Error'
END CATCH
GO
The result is an error from SQL Server:
Msg 208, Level 16, State 1, Line 4
Invalid object name 'XYX'.
When
you use dynamic SQL or a stored procedure, these types of compile
errors are caught because they are part of the current level of
execution. Each of the following SQL blocks will execute the CATCH block.
Listing 2. Catching syntax and recompilation errors in dynamic SQL with exception handlers
-- Dynamic SQL Example
BEGIN TRY
EXEC sp_executesql 'SELECT * * FROM XYX'
END TRY
BEGIN CATCH
PRINT 'Error'
END CATCH
GO
-- Stored Procedure Example
CREATE PROCEDURE MyErrorProc
AS
SELECT * FROM XYX
GO
BEGIN TRY
EXEC MyErrorProc
END TRY
BEGIN CATCH
PRINT 'Error'
END CATCH
GO
