Using TRY...CATCH in Transact-SQL

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

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

You can use TRY…CATCH to handle errors that occur during compilation or statement-level recompilation by executing the error-generating code in a separate batch within the TRY block. For example, you do this by placing the code in a stored procedure or by executing a dynamic Transact-SQL statement using sp_executesql. This allows TRY…CATCH to catch the error at a higher level of execution than the error occurrence. For example, the following code shows a stored procedure that generates an object name resolution error. The batch that contains the TRY…CATCH construct is executing at a higher level than the stored procedure; and the error, which occurs at a lower level, is caught.

USE AdventureWorks;
GO

-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ('usp_MyError', 'P') IS NOT NULL
    DROP PROCEDURE usp_MyError;
GO

CREATE PROCEDURE usp_MyError
AS
    -- This SELECT statement will generate
    -- an object name resolution error.
    SELECT * FROM NonExistentTable;
GO

BEGIN TRY
    -- Run the stored procedure.
    EXECUTE usp_MyError;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

Here is the result set.

ErrorNumber ErrorMessage
----------- ---------------------------------------
208         Invalid object name 'NonExistentTable'.

For more information, see Deferred Name Resolution and Compilation and the "Recompiling Execution Plans" section in Execution Plan Caching and Reuse.

Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such as modifying data or trying to roll back to a savepoint. However, in this state, the locks acquired by the transaction are maintained, and the connection is also kept open. The effects of the transaction are not reversed until a ROLLBACK statement is issued, or until the batch ends and the transaction is automatically rolled back by the Database Engine. 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.

A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block.

The code in a CATCH block should test for the state of a transaction by using the XACT_STATE function. XACT_STATE returns a -1 if the session has an uncommittable transaction. The CATCH block must not perform any actions that would generate writes to the log if XACT_STATE returns a -1. The following code example generates an error from a DDL statement and uses XACT_STATE to test the state of a transaction in order to take the most appropriate action.

USE AdventureWorks;
GO

-- Verify that the table does not exist.
IF OBJECT_ID (N'my_books', N'U') IS NOT NULL
    DROP TABLE my_books;
GO

-- Create table my_books.
CREATE TABLE my_books
    (
    Isbn        int PRIMARY KEY,
    Title       NVARCHAR(100)
    );
GO

BEGIN TRY
    BEGIN TRANSACTION;
        -- This statement will generate an error because the 
        -- column author does not exist in the table.
        ALTER TABLE my_books
            DROP COLUMN author;
    -- If the DDL statement succeeds, commit the transaction.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;

    -- Test XACT_STATE for 1 or -1.
    -- XACT_STATE = 0 means there is no transaction and
    -- a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state. ' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is active and valid.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT
            N'The transaction is committable. ' +
            'Committing transaction.'
        COMMIT TRANSACTION;   
    END;
END CATCH;
GO

TRY…CATCH can be used to handle deadlocks. The 1205 deadlock victim error can be caught by the CATCH block and the transaction can be rolled back until the threads become unlocked. For more information about deadlocking, see Deadlocking.

The following example shows how TRY…CATCH can be used to handle deadlocks. This first section creates a table that will be used to demonstrate a deadlock state and a stored procedure that will be used to print error information.

USE AdventureWorks;
GO

-- Verify that the table does not exist.
IF OBJECT_ID (N'my_sales',N'U') IS NOT NULL
    DROP TABLE my_sales;
GO

-- Create and populate the table for deadlock simulation.
CREATE TABLE my_sales 
    (
    Itemid       INT PRIMARY KEY,
    Sales        INT not null
    );
GO

INSERT my_sales (itemid, sales) VALUES (1, 1);
INSERT my_sales (itemid, sales) VALUES (2, 1);
GO
  
-- Verify that the stored procedure for error printing
-- does not exist.
IF OBJECT_ID (N'usp_MyErrorLog',N'P') IS NOT NULL
    DROP PROCEDURE usp_MyErrorLog;
GO

-- Create a stored procedure for printing error information.
CREATE PROCEDURE usp_MyErrorLog
AS
    PRINT 
        'Error ' + CONVERT(VARCHAR(50), ERROR_NUMBER()) +
        ', Severity ' + CONVERT(VARCHAR(5), ERROR_SEVERITY()) +
        ', State ' + CONVERT(VARCHAR(5), ERROR_STATE()) + 
        ', Line ' + CONVERT(VARCHAR(5), ERROR_LINE());
    PRINT 
        ERROR_MESSAGE();
GO

The following code scripts for session 1 and session 2 run simultaneously in two separate SQL Server Management Studio connections. Both sessions try to update the same rows in the table. One of the sessions will succeed with the update operation during the first attempt, and the other session will be selected as the deadlock victim. The deadlock victim error will cause execution to jump to the CATCH block and the transaction will enter an uncommittable state. Inside the CATCH block, the deadlock victim can roll back the transaction and retry updating the table until the update succeeds or the retry limit is reached, whichever happens first.

USE AdventureWorks;
GO
-- Declare and set variable
-- to track number of retries
-- to try before exiting.
DECLARE @retry INT;
SET @retry = 5;
-- Keep trying to update 
-- table if this task is 
-- selected as the deadlock 
-- victim.
WHILE (@retry > 0)
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 1;
        WAITFOR DELAY '00:00:13';
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 2;
        SET @retry = 0;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH 
        -- Check error number.
        -- If deadlock victim error,
        -- then reduce retry count
        -- for next update retry. 
        -- If some other error
        -- occurred, then exit
        -- retry WHILE loop.
        IF (ERROR_NUMBER() = 1205)
            SET @retry = @retry - 1;
        ELSE
            SET @retry = -1;
        -- Print error information.
        EXECUTE usp_MyErrorLog;
  
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
    END CATCH;
END; -- End WHILE loop.
GO

USE AdventureWorks;
GO
-- Declare and set variable
-- to track number of retries
-- to try before exiting.
DECLARE @retry INT;
SET @retry = 5;
--Keep trying to update 
-- table if this task is 
-- selected as the deadlock 
-- victim.
WHILE (@retry > 0)
BEGIN
    BEGIN TRY
       BEGIN TRANSACTION;
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 2;
        WAITFOR DELAY '00:00:07';
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 1;
        SET @retry = 0;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH 
        -- Check error number.
        -- If deadlock victim error,
        -- then reduce retry count
        -- for next update retry. 
        -- If some other error
        -- occurred, then exit
        -- retry WHILE loop.
        IF (ERROR_NUMBER() = 1205)
            SET @retry = @retry - 1;
        ELSE
            SET @retry = -1;
        -- Print error information.
        EXECUTE usp_MyErrorLog;
  
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
    END CATCH;
END; -- End WHILE loop.
GO

RAISERROR can be used in either the TRY or CATCH block of a TRY…CATCH construct to affect error-handling behavior.

RAISERROR that has a severity of 11 to 19 executed inside a TRY block causes control to transfer to the associated CATCH block. RAISERROR that has a severity of 11 to 19 executed inside a CATCH block returns an error to the calling application or batch. In this way, RAISERROR can be used to return information to the caller about the error that caused the CATCH block to execute. Error information provided by the TRY…CATCH error functions can be captured in the RAISERROR message, including the original error number; however, the error number for RAISERROR must be >= 50000.

RAISERROR that has a severity 10 or lower returns an informational message to the calling batch or application without invoking a CATCH block.

RAISERROR that has a severity 20 or higher closes the database connection without invoking the CATCH block.

The following code example shows how RAISERROR can be used inside a CATCH block to return the original error information to the calling application or batch. The stored procedure usp_GenerateError executes a DELETE statement inside a TRY block that generates a constraint violation error. The error causes execution to transfer to the associated CATCH block inside usp_GenerateError where the stored procedure usp_RethrowError is executed to raise the constraint violation error information using RAISERROR. This error generated by RAISERROR is returned to the calling batch where usp_GenerateError was executed and causes execution to transfer to the associated CATCH block in the calling batch.

USE AdventureWorks;
GO

-- Verify that stored procedure does not exist.
IF OBJECT_ID (N'usp_RethrowError',N'P') IS NOT NULL
    DROP PROCEDURE usp_RethrowError;
GO

-- Create the stored procedure to generate an error using 
-- RAISERROR. The original error information is used to
-- construct the msg_str for RAISERROR.
CREATE PROCEDURE usp_RethrowError AS
    -- Return if there is no error information to retrieve.
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Build the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR 
        (
        @ErrorMessage, 
        @ErrorSeverity, 
        1,               
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
        );
GO

-- Verify that stored procedure does not exist.
IF OBJECT_ID (N'usp_GenerateError',N'P') IS NOT NULL
    DROP PROCEDURE usp_GenerateError;
GO

-- Create a stored procedure that generates a constraint violation
-- error. The error is caught by the CATCH block where it is 
-- raised again by executing usp_RethrowError.
CREATE PROCEDURE usp_GenerateError 
AS 
    BEGIN TRY
        -- A FOREIGN KEY constraint exists on the table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;
    END TRY
    BEGIN CATCH
        -- Call the procedure to raise the original error.
        EXEC usp_RethrowError;
    END CATCH;
GO

-- In the following batch, an error occurs inside 
-- usp_GenerateError that invokes the CATCH block in
-- usp_GenerateError. RAISERROR inside this CATCH block
-- generates an error that invokes the outer CATCH
-- block in the calling batch.
BEGIN TRY  -- outer TRY
    -- Call the procedure to generate an error.
    EXECUTE usp_GenerateError;
END TRY
BEGIN CATCH  -- Outer CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH;
GO

To change the flow of execution, GOTO can be used within a TRY block or a CATCH block. GOTO can also be used to exit a TRY block or a CATCH block; however, GOTO cannot be used to enter a TRY block or a CATCH block.

The ErrorLog table records information about an error number, error severity, error state, name of the stored procedure or trigger where the error occurred, line number at which the error occurred, and the complete text of the error message. It also records the date and time at which the error occurred, and the user name which executed the error-generating routine. This table is populated when the stored procedure uspLogError is executed in the scope of the CATCH block of a TRY…CATCH construct. For more information, see ErrorLog Table (AdventureWorks).

The stored procedure uspLogError logs error information in the ErrorLog table about the error that caused execution to transfer to the CATCH block of a TRY…CATCH construct. For uspLogError to insert error information into the ErrorLog table, the following conditions must exist:

• uspLogError is executed within the scope of a CATCH block.

• If the current transaction is in an uncommittable state, the transaction is rolled back before executing uspLogError.

The output parameter @ErrorLogID of uspLogError returns the ErrorLogID of the row inserted by uspLogError into the ErrorLog table. The default value of @ErrorLogID is 0. The following example shows the code for uspLogError. For more information, see Stored Procedures in AdventureWorks.

CREATE PROCEDURE [dbo].[uspLogError] 
    @ErrorLogID [int] = 0 OUTPUT  -- Contains the ErrorLogID of the row inserted
                                  -- by uspLogError in the ErrorLog table.

AS
BEGIN
    SET NOCOUNT ON;

    -- Output parameter value of 0 indicates that error 
    -- information was not logged.
    SET @ErrorLogID = 0;

    BEGIN TRY
        -- Return if there is no error information to log.
        IF ERROR_NUMBER() IS NULL
            RETURN;

        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
            RETURN;
        END;

        INSERT [dbo].[ErrorLog] 
            (
            [UserName], 
            [ErrorNumber], 
            [ErrorSeverity], 
            [ErrorState], 
            [ErrorProcedure], 
            [ErrorLine], 
            [ErrorMessage]
            ) 
        VALUES 
            (
            CONVERT(sysname, CURRENT_USER), 
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
            );

        -- Pass back the ErrorLogID of the row inserted
        SELECT @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END; 

The stored procedure uspPrintError prints information about the error that caused execution to transfer to the CATCH block of a TRY…CATCH construct. uspPrintError should be executed in the scope of a CATCH block; otherwise, the procedure returns without printing any error information. The following example shows the code for uspPrintError. For more information, see Stored Procedures in AdventureWorks.

CREATE PROCEDURE [dbo].[uspPrintError] 
AS
BEGIN
    SET NOCOUNT ON;

    -- Print error information. 
    PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
          ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ', State ' + CONVERT(varchar(5), ERROR_STATE()) + 
          ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + 
          ', Line ' + CONVERT(varchar(5), ERROR_LINE());
    PRINT ERROR_MESSAGE();
END;

The following example demonstrates the AdventureWorks error-handling solution. The code inside the TRY block tries to delete the record with ProductID 980 in the Production.Product table. A FOREIGN KEY constraint on the table prevents the DELETE statement from succeeding and a constraint violation error is generated. This error causes execution to transfer to the CATCH block. Inside the CATCH block, the following actions occur:

• uspPrintError prints the error information.

• After the transaction is rolled back, uspLogError enters the error information in the ErrorLog table and returns the ErrorLogID of the inserted row into the @ErrorLogID OUTPUT parameter.

USE AdventureWorks;
GO

-- Variable to store ErrorLogID value of the row
-- inserted in the ErrorLog table by uspLogError 
DECLARE @ErrorLogID INT;

BEGIN TRY
    BEGIN TRANSACTION;

    -- A FOREIGN KEY constraint exists on this table. This 
    -- statement will generate a constraint violation error.
    DELETE FROM Production.Product
        WHERE ProductID = 980;

    -- If the delete operation succeeds, commit the transaction.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Call procedure to print error information.
    EXECUTE dbo.uspPrintError;

    -- Roll back any active or uncommittable transactions before
    -- inserting information in the ErrorLog.
    IF XACT_STATE() <> 0
    BEGIN
        ROLLBACK TRANSACTION;
    END

    EXECUTE dbo.uspLogError @ErrorLogID = @ErrorLogID OUTPUT;
END CATCH; 

-- Retrieve logged error information.
SELECT * FROM dbo.ErrorLog WHERE ErrorLogID = @ErrorLogID;
GO

The following example shows using nested TRY…CATCH constructs.

BEGIN TRY
    BEGIN TRY
        SELECT CAST('invalid_date' AS datetime)
    END TRY
    BEGIN CATCH 
        PRINT 'Inner TRY error number: ' +    
            CONVERT(varchar,ERROR_NUMBER()) + ' on line: ' +
            CONVERT(varchar, ERROR_LINE())
    END CATCH
    SELECT CAST('invalid_int' AS int)
END TRY
BEGIN CATCH
    PRINT 'Outer TRY error mumber: ' + CONVERT(varchar,ERROR_NUMBER())+ 
            ' on line: ' + CONVERT(varchar, ERROR_LINE())
END CATCH

Here is the result set.

Inner TRY error number: 241 on line: 3

Outer TRY error number: 245 on line: 9