SQL SERVER 2005
Explanation of TRY&CATCH and ERROR Handling
SQL Server 2005 offers a more robust set of tools for handling errors than
in previous versions of SQL Server. Deadlocks, which are virtually impossible
to handle at the database level in SQL Server 2000, can now be handled with
ease. By taking advantage of these new features, you can focus more on IT
business strategy development and less on what needs to happen when errors
occur. In SQL Server 2005, @@ERROR variable is no longer needed after every
statement executed, as was the case in SQL Server 2000. SQL Server 2005
provides the TRY&CATCH construct, which is already present in many modern
programming languages. TRY/CATCH helps to write logic separate the action and
error handling code. The code meant for the action is enclosed in the TRY block
and the code for error handling is enclosed in the CATCH block. In case the
code within the TRY block fails, the control automatically jumps to the CATCH
block, letting the transaction roll back and resume execution. In addition to
this, the CATCH block captures and provides error information that shows you
the ID, message text, state, severity and transaction state of an error.
Functions to be used in CATCH block are :
- ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
- ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
- ERROR_STATE: returns the state number of the error.
- ERROR_LINE: returns the line number where the error occurred.
- ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
- ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
You can use these functions anywhere inside a CATCH block, and they will
return information regarding the error that has occurred. These functions will
return the value null outside of the CATCH block.
Syntax:
BEGIN TRY
{ sql_statement |
statement_block }
END TRY
BEGIN CATCH
{ sql_statement |
statement_block }
END CATCH
The TRY or CATCH block can contain a single T-SQL statement or a series of
statements. The CATCH block must follow immediately after the TRY block. The
TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH
block cannot span an IF/ELSE statement.
Example of TRY&CATCH:
BEGIN TRY
DECLARE @X INT
-- Divide by zero to generate Error
SET @X = 1/0
PRINT 'Command after error in TRY block'
END TRY
BEGIN CATCH
PRINT 'Error Detected'
END CATCH
PRINT 'Command after TRY/CATCH blocks'
Above code will return following result:
Error Detected
Command after TRY/CATCH blocks
If all the statements within the TRY block are executed successfully, then
processing does not enter the CATCH block, but instead skips over the CATCH
block and executes the first statement following the END CATCH statement.
Removing SET statement in above code PRINT Error Detected statement is not
executed, but the PRINT statement within the TRY block is executed, as well as
the PRINT statement after the TRY/CATCH block. TRY/CATCH blocks can be
nested.
Limitation of TRY&CATCH:
- Compiled errors are not caught.
- Deferred name resolution errors created by statement level recompilations. (If process is terminated by Kill commands or broken client connections TRY&CATCH will be not effective)
- Errors with a severity greater than 10 that do not terminate their database connection are caught in the TRY/CATCH block.
For errors that are not trapped, SQL Server 2005 passes control back to
the application immediately, without executing any CATCH block code.
Similar example of TRY&CATCH which includes all the ERROR
functions:
USE AdventureWorks;
GO
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/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;
GO