Errors in Transact-SQL code can be processed by using a TRY…CATCH construct similar to the exception-handling features of the Microsoft Visual C++ and Microsoft Visual C# languages. A TRY…CATCH construct consists of two parts: a TRY block and a CATCH block. When an error condition is detected in a Transact-SQL statement that is inside a TRY block, control is passed to a CATCH block where the error can be processed.
After the CATCH block handles the exception, control is then transferred to the first Transact-SQL statement that follows the END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is returned to the code that invoked the stored procedure or trigger. Transact-SQL statements in the TRY block following the statement that generates an error will not be executed.
If there are no errors inside the TRY block, control passes to the statement immediately after the associated END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed to the statement that invoked the stored procedure or trigger.
A TRY block starts with the BEGIN TRY statement and ends with the END TRY statement. One or more Transact-SQL statements can be specified between the BEGIN TRY and END TRY statements.
A TRY block must be followed immediately by a CATCH block. A CATCH block starts with the BEGIN CATCH statement and ends with the END CATCH statement. In Transact-SQL, each TRY block is associated with only one CATCH block.
Working with TRY…CATCH
When you use the TRY…CATCH construct, consider the following guidelines and suggestions:
Each TRY…CATCH construct must be inside a single batch, stored procedure, or trigger. For example, you cannot place aTRYblock in one batch and the associatedCATCHblock in another batch. The following script would generate an error:
id="ctl00_MTCS_main_ctl51_ctl00_ctl00_">
BEGIN TRY
SELECT *
FROM sys.messages
WHERE message_id = 21;
END TRY
GO
-- The previous GO breaks the script into two batches,
-- generating syntax errors. The script runs if this GO
-- is removed.
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO
A TRY block must be immediately followed by a CATCH block.
TRY…CATCH constructs can be nested. This means that TRY…CATCH constructs can be placed inside other TRY and CATCH blocks. When an error occurs within a nested TRY block, program control is transferred to the CATCH block that is associated with the nested TRY block.
To handle an error that occurs within a given CATCH block, write a TRY…...CATCH block within the specified CATCH block.
Errors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY…CATCH block. However, TRY…CATCH will handle errors with a severity of 20 or higher as long as the connection is not closed.
Errors that have a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY…CATCH blocks.
Attentions will terminate a batch even if the batch is within the scope of a TRY…CATCH construct. This includes an attention sent by the Microsoft Distributed Transaction Coordinator (MS DTC) when a distributed transaction fails. MS DTC manages distributed transactions.
 Note: |
|---|
If a distributed transaction executes within the scope of a TRY block and an error occurs, execution is transferred to the associated CATCH block. The distributed transaction enters an uncommittable state. Execution within the CATCH block may be interrupted by the Microsoft Distributed Transaction Coordinator which manages distributed transactions. When the error occurs, MS DTC asynchronously notifies all servers participating in the distributed transaction, and terminates all tasks involved in the distributed transaction. This notification is sent in the form of an attention, which is not handled by a TRY…CATCH construct, and the batch is ended. When a batch finishes running, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back For more information about distributed transactions, see Distributed Transactions (Database Engine). |
Error Functions
TRY…CATCH uses the following error functions to capture error information:
ERROR_NUMBER() returns the error number.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.
ERROR_SEVERITY() returns the error severity.
ERROR_STATE() returns the error state number.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
Error information is retrieved by using these functions from anywhere in the scope of the CATCH block of a TRY…CATCH construct. The error functions will return NULL if called outside the scope of a CATCH block. Error functions can be referenced inside a stored procedure and can be used to retrieve error information when the stored procedure is executed in the CATCH block. By doing this, you do not have to repeat the error handling code in every CATCH block. In the follow code example, the SELECT statement in the TRY block will generate a divide-by-zero error. The error will be handled by the CATCH block, which uses a stored procedure to return error information.
USE AdventureWorks;
GO
-- Verify that the stored procedure does not exist.
IF OBJECT_ID ('usp_GetErrorInfo', 'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO
-- Create a procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
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;
GO
BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
GO
Compile and Statement-level Recompile Errors
There are two types of errors that will not be handled by TRY…CATCH if the error occurs in the same execution level as the TRY…CATCH construct:
Compile errors, such as syntax errors that prevent a batch from executing.
Errors that occur during statement-level recompilation, such as object name resolution errors that happen after compilation due to deferred name resolution.
When the batch, stored procedure, or trigger that contains the TRY…CATCH construct generates one of these errors, the TRY…CATCH construct does not handle these errors. These errors will return to the application or batch that called the error-generating routine. For example, the following code example shows a SELECT statement that causes a syntax error. If this code is executed in the SQL Server Management Studio Query Editor, execution will not start because the batch fails to compile. The error will be returned to the Query Editor and will not get caught by TRY…CATCH.
USE AdventureWorks;
GO
BEGIN TRY
-- This PRINT statement will not run because the batch
-- does not begin execution.
PRINT N'Starting execution';
-- This SELECT statement contains a syntax error that
-- stops the batch from compiling successfully.
SELECT ** FROM HumanResources.Employee;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
Unlike the syntax error in the previous example, an error that occurs during statement-level recompilation will not prevent the batch from compiling, but it will terminate the batch as soon as recompilation for the statement fails. For example, if a batch has two statements and the second statement references a table that does not exist, deferred name resolution causes the batch to compile successfully and start execution without binding the missing table to the query plan until that statement is recompiled. The batch stops running when it gets to the statement that references the missing table and returns an error. This type of error will not be handled by a TRY…CATCH construct at the same level of execution at which the error occurred. The following example demonstrates this behavior.
USE AdventureWorks;
GO
BEGIN TRY
-- This PRINT statement will run because the error
-- occurs at the SELECT statement.
PRINT N'Starting execution';
-- This SELECT statement will generate an object name
-- resolution error because the table does not exist.
SELECT * FROM NonExistentTable;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
