TRY...CATCH (Transact-SQL)TRY...CATCH (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

Transact-SQLTransact-SQL 实现与 MicrosoftMicrosoft Visual C# 和 MicrosoftMicrosoft Visual C++ 语言中的异常处理类似的错误处理。Implements error handling for Transact-SQLTransact-SQL that is similar to the exception handling in the MicrosoftMicrosoft Visual C# and MicrosoftMicrosoft Visual C++ languages. Transact-SQLTransact-SQL 语句组可以包含在 TRY 块中。A group of Transact-SQLTransact-SQL statements can be enclosed in a TRY block. 如果 TRY 块内部发生错误,则会将控制传递给 CATCH 块中包含的另一个语句组。If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

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

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

sql_statement sql_statement
任何 Transact-SQLTransact-SQL 语句。Is any Transact-SQLTransact-SQL statement.

statement_block statement_block
批处理或包含于 BEGIN…END 块中的任何 Transact-SQLTransact-SQL 语句组。Any group of Transact-SQLTransact-SQL statements in a batch or enclosed in a BEGIN...END block.

备注Remarks

TRY…CATCH 构造可对严重程度高于 10 但不关闭数据库连接的所有执行错误进行缓存。A TRY...CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

TRY 块后必须紧跟相关联的 CATCH 块。A TRY block must be immediately followed by an associated CATCH block. 在 END TRY 和 BEGIN CATCH 语句之间放置任何其他语句都将生成语法错误。Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.

TRY…CATCH 构造不能跨越多个批处理。A TRY...CATCH construct cannot span multiple batches. TRY…CATCH 构造不能跨越多个 Transact-SQLTransact-SQL 语句块。A TRY...CATCH construct cannot span multiple blocks of Transact-SQLTransact-SQL statements. 例如,TRY…CATCH 构造不能跨越 Transact-SQLTransact-SQL 语句的两个 BEGIN…END 块,且不能跨越 IF…ELSE 构造。For example, a TRY...CATCH construct cannot span two BEGIN...END blocks of Transact-SQLTransact-SQL statements and cannot span an IF...ELSE construct.

如果 TRY 块所包含的代码中没有错误,则当 TRY 块中最后一个语句完成运行时,会将控制传递给紧跟在相关联的 END CATCH 语句之后的语句。If there are no errors in the code that is enclosed in a TRY block, when the last statement in the TRY block has finished running, control passes to the statement immediately after the associated END CATCH statement.

如果 TRY 块所包含的代码中有错误,则会将控制传递给相关联的 CATCH 块的第一个语句。If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block. 当 CATCH 块中的代码完成时,会将控制传递给紧跟在 END CATCH 语句之后的语句。When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement.

备注

如果 END CATCH 语句是存储过程或触发器的最后一个语句,控制将回到调用该存储过程或运行该触发器的语句。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.

由 CATCH 块捕获的错误不会返回到调用应用程序。Errors trapped by a CATCH block are not returned to the calling application. 如果错误消息的任何部分都必须返回到应用程序,则 CATCH 块中的代码必须使用 SELECT 结果集或 RAISERROR 和 PRINT 语句之类的机制执行此操作。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 构造可以是嵌套式的。TRY...CATCH constructs can be nested. TRY 块或 CATCH 块均可包含嵌套的 TRY…CATCH 构造。Either a TRY block or a CATCH block can contain nested TRY...CATCH constructs. 例如,CATCH 块可以包含内嵌的 TRY…CATCH 构造,以处理 CATCH 代码所遇到的错误。For example, a CATCH block can contain an embedded TRY...CATCH construct to handle errors encountered by the CATCH code.

处理 CATCH 块中遇到的错误的方法与处理任何其他位置生成的错误一样。Errors encountered in a CATCH block are treated like errors generated anywhere else. 如果 CATCH 块包含嵌套的 TRY…CATCH 构造,则嵌套的 TRY 块中的任何错误都会将控制传递给嵌套的 CATCH 块。If the CATCH block contains a nested TRY...CATCH construct, any error in the nested TRY block will pass control to the nested CATCH block. 如果没有嵌套的 TRY…CATCH 构造,则会将错误传递回调用方。If there is no nested TRY...CATCH construct, the error is passed back to the caller.

TRY…CATCH 构造可以从存储过程或触发器(由 TRY 块中的代码执行)捕捉未处理的错误。TRY...CATCH constructs catch unhandled errors from stored procedures or triggers executed by the code in the TRY block. 或者,存储过程或触发器也可以包含其自身的 TRY…CATCH 构造,以处理由其代码生成的错误。Alternatively, the stored procedures or triggers can contain their own TRY...CATCH constructs to handle errors generated by their code. 例如,当 TRY 块执行存储过程且存储过程中发生错误时,可以使用以下方式处理错误: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:

  • 如果存储过程不包含自己的 TRY…CATCH 构造,错误会将控制返回到与包含 EXECUTE 语句的 TRY 块相关联的 CATCH 块。If the stored procedure does not contain its own TRY...CATCH construct, the error returns control to the CATCH block associated with the TRY block that contains the EXECUTE statement.

  • 如果存储过程包含 TRY…CATCH 构造,则错误会将控制传输给存储过程中的 CATCH 块。If the stored procedure contains a TRY...CATCH construct, the error transfers control to the CATCH block in the stored procedure. 当 CATCH 块代码完成时,控制会传递回调用存储过程的 EXECUTE 语句之后的语句。When the CATCH block code finishes, control is passed back to the statement immediately after the EXECUTE statement that called the stored procedure.

不能使用 GOTO 语句输入 TRY 或 CATCH 块,GOTO statements cannot be used to enter a TRY or CATCH block. 使用 GOTO 语句可以跳转至同一 TRY 或 CATCH 块内的某个标签,或离开 TRY 或 CATCH 块。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.

不能在用户定义函数内使用 TRY…CATCH 构造。The TRY...CATCH construct cannot be used in a user-defined function.

检索错误信息Retrieving Error Information

在 CATCH 块的作用域内,可以使用以下系统函数来获取导致 CATCH 块执行的错误消息: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:

如果是在 CATCH 块的作用域之外调用这些函数,则这些函数返回空值。These functions return NULL if they are called outside the scope of the CATCH block. 可以从 CATCH 块作用域内的任何位置使用这些函数检索错误消息。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. CATCH 构造的 TRY...CATCH 块中,调用了该存储过程并返回有关错误的信息。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;   

ERROR_* 函数也适用于本机编译的存储过程内的 CATCH 块。The ERROR_* functions also work in a CATCH block inside a natively compiled stored procedure.

不受 TRY…CATCH 构造影响的错误Errors Unaffected by a TRY...CATCH Construct

TRY…CATCH 构造在下列情况下不捕获错误:TRY...CATCH constructs do not trap the following conditions:

  • 严重级别为 10 或更低的警告或信息性消息。Warnings or informational messages that have a severity of 10 or lower.

  • 严重级别为 20 或更高且终止会话的 SQL Server 数据库引擎SQL Server Database Engine任务处理的错误。Errors that have a severity of 20 or higher that stop the SQL Server 数据库引擎SQL Server Database Engine task processing for the session. 如果所发生错误的严重级别为 20 或更高,而数据库连接未中断,则 TRY…CATCH 将处理该错误。If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY...CATCH will handle the error.

  • 需要关注的消息,如客户端中断请求或客户端连接中断。Attentions, such as client-interrupt requests or broken client connections.

  • 当系统管理员使用 KILL 语句终止会话时。When the session is ended by a system administrator by using the KILL statement.

如果以下类型的错误的发生级别与 TRY…CATCH 构造的执行等级相同,则 CATCH 块不会处理这些错误:The following types of errors are not 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.

如果某个错误在 TRY 块内的编写或语句级别重新编写过程中并在较低的执行级别(例如,执行 sp_executesql 或用户定义存储过程时)发生,则该错误会在低于 TRY…CATCH 构造的级别上发生,并由相关联的 CATCH 块处理。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.

以下示例显示在存储过程中执行相同的 SELECT 语句时,由 TRY...CATCH 语句生成的对象名解析错误是如何不被 CATCH 构造捕捉,却被 SELECT 块捕捉的。The following example shows how an object name resolution error generated by a SELECT statement is not 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  

未捕捉错误,控制从 TRY...CATCH 构造传递给下一更高级别。The error is not caught and control passes out of the TRY...CATCH construct to the next higher level.

在存储过程内运行 SELECT 语句将导致错误在低于 TRY 块的级别上发生。Running the SELECT statement inside a stored procedure will cause the error to occur at a level lower than the TRY block. 该错误将由 TRY...CATCH 构造处理。The error will be 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;  

不可提交的事务和 XACT_STATEUncommittable Transactions and XACT_STATE

如果 TRY 块内生成的错误导致当前事务的状态失效,则将该事务归类为不可提交的事务。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. 如果通常在 TRY 块外中止事务的错误在 TRY 内发生时,就会导致事务进入不可提交状态。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. 不可提交的事务只能执行读操作或 ROLLBACK TRANSACTION。An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. 该事务不能执行任何可能生成写操作或 COMMIT TRANSACTION 的 Transact-SQLTransact-SQL 语句。The transaction cannot execute any Transact-SQLTransact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. 如果事务被分类为不可提交的事务,则 XACT_STATE 函数会返回值 -1。The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. 当批处理结束时,数据库引擎Database Engine将回滚所有不可提交的活动事务。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 will be sent to the client application. 该消息指示检测到并回滚了一个不可提交的事务。This indicates that an uncommittable transaction was detected and rolled back.

有关不可提交的事务和 XACT_STATE 函数的详细信息,请参阅 XACT_STATE (Transact-SQL)For more information about uncommittable transactions and the XACT_STATE function, see XACT_STATE (Transact-SQL).

示例Examples

A.A. 使用 TRY...CATCHUsing TRY...CATCH

以下示例显示一个会生成被零除错误的 SELECT 语句。The following example shows a SELECT statement that will generate a divide-by-zero error. 该错误会使执行跳转到关联的 CATCH 块。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.B. 在事务内使用 TRY…CATCHUsing TRY...CATCH in a transaction

以下示例显示 TRY...CATCH 块在事务内的工作方式。The following example shows how a TRY...CATCH block works inside a transaction. TRY 块内的语句会生成违反约束的错误。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.C. 将 TRY…CATCH 与 XACT_STATE 配合使用Using TRY...CATCH with XACT_STATE

以下示例显示如何使用 TRY...CATCH 构造来处理事务内发生的错误。The following example shows how to use the TRY...CATCH construct to handle errors that occur inside a transaction. XACT_STATE 函数确定应提交事务还是应回滚事务。The XACT_STATE function determines whether the transaction should be committed or rolled back. 在本示例中,SET XACT_ABORT 状态为 ONIn 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  

示例:Azure Synapse AnalyticsAzure Synapse Analytics并行数据仓库Parallel Data WarehouseExamples: Azure Synapse AnalyticsAzure Synapse Analytics and 并行数据仓库Parallel Data Warehouse

D.D. 使用 TRY...CATCHUsing TRY...CATCH

以下示例显示一个会生成被零除错误的 SELECT 语句。The following example shows a SELECT statement that will generate a divide-by-zero error. 该错误会使执行跳转到关联的 CATCH 块。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_MESSAGE() AS ErrorMessage;  
END CATCH;  
GO  

另请参阅See Also

THROW (Transact-SQL) THROW (Transact-SQL)
数据库引擎错误严重性 Database Engine Error Severities
ERROR_LINE (Transact-SQL) ERROR_LINE (Transact-SQL)
ERROR_MESSAGE (Transact-SQL) ERROR_MESSAGE (Transact-SQL)
ERROR_NUMBER (Transact-SQL) ERROR_NUMBER (Transact-SQL)
ERROR_PROCEDURE (Transact-SQL) ERROR_PROCEDURE (Transact-SQL)
ERROR_SEVERITY (Transact-SQL) ERROR_SEVERITY (Transact-SQL)
ERROR_STATE (Transact-SQL&) ERROR_STATE (Transact-SQL)
RAISERROR (Transact-SQL) RAISERROR (Transact-SQL)
@@ERROR (Transact-SQL) @@ERROR (Transact-SQL)
GOTO (Transact-SQL) GOTO (Transact-SQL)
BEGIN...END (Transact-SQL) BEGIN...END (Transact-SQL)
XACT_STATE (Transact-SQL) XACT_STATE (Transact-SQL)
SET XACT_ABORT (Transact-SQL)SET XACT_ABORT (Transact-SQL)