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

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

實作類似於 Transact-SQLTransact-SQL Visual C# 與 MicrosoftMicrosoft Visual C++ 語言中之例外狀況處理的 MicrosoftMicrosoft 錯誤處理。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  
[ ; ]  

引數Arguments

sql_statementsql_statement
這是任何 Transact-SQLTransact-SQL 陳述式。Is any Transact-SQLTransact-SQL statement.

statement_blockstatement_block
在批次或 BEGIN...END 區塊中的任何 Transact-SQLTransact-SQL 陳述式群組。Any group of Transact-SQLTransact-SQL statements in a batch or enclosed in a BEGIN...END block.

RemarksRemarks

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. 如果 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 區塊中的程式碼完成時,控制權會傳給緊接在 END CATCH 陳述式之後的陳述式。When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement. 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:

  • ERROR_NUMBER() 會傳回錯誤碼。ERROR_NUMBER() returns the number of the error.

  • ERROR_SEVERITY() 會傳回嚴重性。ERROR_SEVERITY() returns the severity.

  • ERROR_STATE() 會傳回錯誤狀態碼。ERROR_STATE() returns the error state number.

  • ERROR_PROCEDURE() 會傳回發生錯誤的預存程序或觸發程序的名稱。ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

  • ERROR_LINE() 會傳回常式內造成錯誤的行號。ERROR_LINE() returns the line number inside the routine that caused the error.

  • ERROR_MESSAGE() 會傳回錯誤訊息的完整文字。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.

如果是在 CATCH 區塊範圍之外呼叫這些函數,它們會傳回 NULL。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 Database EngineSQL Server Database Engine 工作處理。Errors that have a severity of 20 or higher that stop the SQL Server Database EngineSQL 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 EngineDatabase Engine 會回復任何使用中無法認可的交易。When a batch finishes, the Database EngineDatabase 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_STATEUsing 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_ABORTONIn 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.  
    IF (XACT_STATE()) = 1  
    BEGIN  
        PRINT  
            N'The transaction is committable.' +  
            'Committing transaction.'  
        COMMIT TRANSACTION;     
    END;  
END CATCH;  
GO  

範例:Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse 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)