TRY...CATCH (Transact-SQL)

Стаття
07/26/2024

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Implements error handling for Transact-SQL that is similar to the exception handling in the C# and Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is usually passed to another group of statements that is enclosed in a CATCH block.

Transact-SQL syntax conventions

Syntax

BEGIN TRY
    { sql_statement | statement_block }
END TRY
BEGIN CATCH
    [ { sql_statement | statement_block } ]
END CATCH
[ ; ]

Arguments

sql_statement

Any Transact-SQL statement.

statement_block

Any group of Transact-SQL statements in a batch or enclosed in a BEGIN...END block.

Remarks

A TRY...CATCH construct catches all execution errors that have a severity higher than 10 that don't close the database connection.

A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.

A TRY...CATCH construct can't span multiple batches. A TRY...CATCH construct can't span multiple blocks of Transact-SQL statements. For example, a TRY...CATCH construct can't span two BEGIN...END blocks of Transact-SQL statements, and can't span an IF...ELSE construct.

If there are no errors in the code that is enclosed in a TRY block, when the last statement in the TRY block finishes, control passes to the statement immediately after the associated END CATCH statement.

If there's an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block. When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement.

Note

If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed back to the statement that called the stored procedure or fired the trigger.

Errors trapped by a CATCH block aren't returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR and PRINT statements.

TRY...CATCH constructs can be nested. Either a TRY block or a CATCH block can contain nested TRY...CATCH constructs. For example, a CATCH block can contain an embedded TRY...CATCH construct to handle errors encountered by the CATCH code.

Errors encountered in a CATCH block are treated like errors generated anywhere else. If the CATCH block contains a nested TRY...CATCH construct, any error in the nested TRY block passes control to the nested CATCH block. If there's no nested TRY...CATCH construct, the error is passed back to the caller.

TRY...CATCH constructs catch unhandled errors from stored procedures or triggers executed by the code in the TRY block. Alternatively, the stored procedures or triggers can contain their own TRY...CATCH constructs to handle errors generated by their code. For example, when a TRY block executes a stored procedure and an error occurs in the stored procedure, the error can be handled in the following ways:

If the stored procedure doesn't contain its own TRY...CATCH construct, the error returns control to the CATCH block associated with the TRY block that contains the EXECUTE statement.
If the stored procedure contains a TRY...CATCH construct, the error transfers control to the CATCH block in the stored procedure. When the CATCH block code finishes, control is passed back to the statement immediately after the EXECUTE statement that called the stored procedure.

GOTO statements can't be used to enter a TRY or CATCH block. GOTO statements can be used to jump to a label inside the same TRY or CATCH block or to leave a TRY or CATCH block.

The TRY...CATCH construct can't be used in a user-defined function.

Retrieve error information

In the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed:

Function	Description
ERROR_NUMBER	Returns the number of the error.
ERROR_SEVERITY	Returns the severity.
ERROR_STATE	Returns the error state number.
ERROR_PROCEDURE	Returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE	Returns the line number inside the routine that caused the error.
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.

These functions return NULL if they're called outside the scope of the CATCH block. Error information can be retrieved by using these functions from anywhere within the scope of the CATCH block. For example, the following script shows a stored procedure that contains error-handling functions. In the CATCH block of a TRY...CATCH construct, the stored procedure is called and information about the error is returned.

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

-- Create 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 error retrieval routine.
    EXECUTE usp_GetErrorInfo;
END CATCH;

The ERROR_* functions also work in a CATCH block inside a natively compiled stored procedure.

Errors unaffected by a TRY...CATCH construct

TRY...CATCH constructs don't trap the following conditions:

Warnings or informational messages that have a severity of 10 or lower.
Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection isn't disrupted, TRY...CATCH handles the error.
Attentions, such as client-interrupt requests or broken client connections.
When a system administrator uses the KILL statement to end the session.

The following types of errors aren't handled by a CATCH block when they occur at the same level of execution as the TRY...CATCH construct:

Compile errors, such as syntax errors, that prevent a batch from running.
Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.
Object name resolution errors

These errors are returned to the level that ran the batch, stored procedure, or trigger.

If an error occurs during compilation or statement-level recompilation at a lower execution level (for example, when executing sp_executesql or a user-defined stored procedure) inside the TRY block, the error occurs at a lower level than the TRY...CATCH construct and will be handled by the associated CATCH block.

The following example shows how an object name resolution error generated by a SELECT statement isn't caught by the TRY...CATCH construct, but is caught by the CATCH block when the same SELECT statement is executed inside a stored procedure.

BEGIN TRY
    -- Table does not exist; object name resolution
    -- error not caught.
    SELECT *
    FROM NonexistentTable;
END TRY

BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH

The error isn't caught and control passes out of the TRY...CATCH construct to the next higher level.

Running the SELECT statement inside a stored procedure causes the error to occur at a level lower than the TRY block. The error is handled by the TRY...CATCH construct.

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

-- Create a stored procedure that will cause an
-- object resolution error.
CREATE PROCEDURE usp_ExampleProc
AS
SELECT *
FROM NonexistentTable;
GO

BEGIN TRY
    EXECUTE usp_ExampleProc;
END TRY

BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Uncommittable transactions and XACT_STATE

If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction can't execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, 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 is sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.

For more information about uncommittable transactions and the XACT_STATE function, see XACT_STATE.

Examples

A. Use TRY...CATCH

The following example shows a SELECT statement that generates a divide-by-zero error. The error causes execution to jump to the associated CATCH block.

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

B. Use TRY...CATCH in a transaction

The following example shows how a TRY...CATCH block works inside a transaction. The statement inside the TRY block generates a constraint violation error.

BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE
    FROM Production.Product
    WHERE ProductID = 980;
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;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

C. Use TRY...CATCH with XACT_STATE

The following example shows how to use the TRY...CATCH construct to handle errors that occur inside a transaction. The XACT_STATE function determines whether the transaction should be committed or rolled back. In this example, SET XACT_ABORT is ON. This makes the transaction uncommittable when the constraint violation error occurs.

-- Check to see whether this stored procedure exists.
IF OBJECT_ID(N'usp_GetErrorInfo', N'P') IS NOT NULL
    DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_LINE() AS ErrorLine,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_MESSAGE() AS ErrorMessage;
GO

-- SET XACT_ABORT ON will cause the transaction to be uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

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 statement succeeds, commit the transaction.
    COMMIT TRANSACTION;
END TRY

BEGIN CATCH
    -- Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;

    -- Test XACT_STATE:
    -- If 1, the transaction is committable.
    -- If -1, the transaction is uncommittable and should
    --     be rolled back.
    -- XACT_STATE = 0 means that 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 committable.
    -- You may want to commit a transaction in a catch block if you want to commit changes to statements that ran prior to the error.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT N'The transaction is committable. Committing transaction.'

        COMMIT TRANSACTION;
    END;
END CATCH;
GO

Поділитися через

TRY...CATCH (Transact-SQL)

Syntax

Arguments

sql_statement

statement_block

Remarks

Retrieve error information

Errors unaffected by a TRY...CATCH construct

Uncommittable transactions and XACT_STATE

Examples

A. Use TRY...CATCH

B. Use TRY...CATCH in a transaction

C. Use TRY...CATCH with XACT_STATE

Зворотний зв’язок

Додаткові ресурси

Поділитися через

TRY...CATCH (Transact-SQL)

Syntax

Arguments

sql_statement

statement_block

Remarks

Retrieve error information

Errors unaffected by a TRY...CATCH construct

Uncommittable transactions and XACT_STATE

Examples

A. Use TRY...CATCH

B. Use TRY...CATCH in a transaction

C. Use TRY...CATCH with XACT_STATE

Related content

Зворотний зв’язок

Додаткові ресурси