@@ERROR (Transact-SQL)@@ERROR (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 陳述式的錯誤號碼。Returns the error number for the last Transact-SQLTransact-SQL statement executed.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

@@ERROR  

傳回類型Return Types

integerinteger

RemarksRemarks

如果先前的 Transact-SQLTransact-SQL 陳述式沒有發現任何錯誤,便傳回 0。Returns 0 if the previous Transact-SQLTransact-SQL statement encountered no errors.

如果先前的陳述式發現錯誤,便傳回錯誤號碼。Returns an error number if the previous statement encountered an error. 如果錯誤是 sys.messages 目錄檢視中的錯誤之一,@@ERROR 會包含這項錯誤在 sys.messages.message_id 資料行中的值。If the error was one of the errors in the sys.messages catalog view, then @@ERROR contains the value from the sys.messages.message_id column for that error. 您可以在 sys.messages 中檢視 @@ERROR 錯誤號碼的相關聯文字。You can view the text associated with an @@ERROR error number in sys.messages.

由於執行每個陳述式時,都會清除再重設 @@ERROR,因此,請在陳述式之後,立即檢查它,或將它儲存在稍後能夠檢查的本機變數中。Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later.

使用 TRY...CATCH 建構來處理錯誤。Use the TRY...CATCH construct to handle errors. TRY...CATCH 建構也支援其他會傳回 @@ERROR 以外之錯誤資訊的系統函數 (ERROR_LINE、ERROR_MESSAGE、ERROR_PROCEDURE、ERROR_SEVERITY 和 ERROR_STATE)。The TRY...CATCH construct also supports additional system functions (ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE) that return more error information than @@ERROR. TRY...CATCH 也支援 ERROR_NUMBER 函數,ERROR_NUMBER 函數並不限於在緊接於產生錯誤的陳述式之後的陳述式中傳回錯誤號碼。TRY...CATCH also supports an ERROR_NUMBER function that is not limited to returning the error number in the statement immediately after the statement that generated an error. 如需詳細資訊,請參閱 TRY...CATCH (Transact-SQL)For more information, see TRY...CATCH (Transact-SQL).

範例Examples

A.A. 利用 @@ERROR 來偵測特定錯誤Using @@ERROR to detect a specific error

下列範例利用 @@ERROR 來檢查 UPDATE 陳述式的 CHECK 條件約束違規 (錯誤號碼 547)。The following example uses @@ERROR to check for a check constraint violation (error #547) in an UPDATE statement.

USE AdventureWorks2012;  
GO  
UPDATE HumanResources.EmployeePayHistory  
    SET PayFrequency = 4  
    WHERE BusinessEntityID = 1;  
IF @@ERROR = 547  
    PRINT N'A check constraint violation occurred.';  
GO  

B.B. 利用 @@ERROR 有條件地結束程序Using @@ERROR to conditionally exit a procedure

下列範例會在預存程序中的 DELETE 陳述式之後,使用 IF...ELSE 陳述式來測試 @@ERRORThe following example uses IF...ELSE statements to test @@ERROR after an DELETE statement in a stored procedure. @@ERROR 變數的值決定了傳給呼叫端程式來指出程序成功或失敗的傳回碼。The value of the @@ERROR variable determines the return code sent to the calling program, indicating success or failure of the procedure.

USE AdventureWorks2012;  
GO  
-- Drop the procedure if it already exists.  
IF OBJECT_ID(N'HumanResources.usp_DeleteCandidate', N'P') IS NOT NULL  
    DROP PROCEDURE HumanResources.usp_DeleteCandidate;  
GO  
-- Create the procedure.  
CREATE PROCEDURE HumanResources.usp_DeleteCandidate   
    (  
    @CandidateID INT  
    )  
AS  
-- Execute the DELETE statement.  
DELETE FROM HumanResources.JobCandidate  
    WHERE JobCandidateID = @CandidateID;  
-- Test the error value.  
IF @@ERROR <> 0   
    BEGIN  
        -- Return 99 to the calling program to indicate failure.  
        PRINT N'An error occurred deleting the candidate information.';  
        RETURN 99;  
    END  
ELSE  
    BEGIN  
        -- Return 0 to the calling program to indicate success.  
        PRINT N'The job candidate has been deleted.';  
        RETURN 0;  
    END;  
GO  

C.C. 搭配 @@ROWCOUNT使用 @@ERRORUsing @@ERROR with @@ROWCOUNT

下列範例搭配 @@ERROR 使用 @@ROWCOUNT 來驗證 UPDATE 陳述式的作業。The following example uses @@ERROR with @@ROWCOUNT to validate the operation of an UPDATE statement. @@ERROR 的值用來針對任何錯誤指示來進行檢查,而 @@ROWCOUNT 則用來確保更新已成功套用至資料表中的資料列。The value of @@ERROR is checked for any indication of an error, and @@ROWCOUNT is used to ensure that the update was successfully applied to a row in the table.

USE AdventureWorks2012;  
GO  
IF OBJECT_ID(N'Purchasing.usp_ChangePurchaseOrderHeader',N'P')IS NOT NULL  
    DROP PROCEDURE Purchasing.usp_ChangePurchaseOrderHeader;  
GO  
CREATE PROCEDURE Purchasing.usp_ChangePurchaseOrderHeader  
    (  
    @PurchaseOrderID INT  
    ,@BusinessEntityID INT  
   )  
AS  
-- Declare variables used in error checking.  
DECLARE @ErrorVar INT;  
DECLARE @RowCountVar INT;  
  
-- Execute the UPDATE statement.  
UPDATE PurchaseOrderHeader   
    SET BusinessEntityID = @BusinessEntityID   
    WHERE PurchaseOrderID = @PurchaseOrderID;  
  
-- Save the @@ERROR and @@ROWCOUNT values in local   
-- variables before they are cleared.  
SELECT @ErrorVar = @@ERROR  
    ,@RowCountVar = @@ROWCOUNT;  
  
-- Check for errors. If an invalid @BusinessEntityID was specified,  
-- the UPDATE statement returns a foreign key violation error #547.  
IF @ErrorVar <> 0  
    BEGIN  
        IF @ErrorVar = 547  
            BEGIN  
                PRINT N'ERROR: Invalid ID specified for new employee.';  
                 RETURN 1;  
            END  
        ELSE  
            BEGIN  
                PRINT N'ERROR: error '  
                    + RTRIM(CAST(@ErrorVar AS NVARCHAR(10)))  
                    + N' occurred.';  
                RETURN 2;  
            END  
    END  
  
-- Check the row count. @RowCountVar is set to 0   
-- if an invalid @PurchaseOrderID was specified.  
IF @RowCountVar = 0  
    BEGIN  
        PRINT 'Warning: The BusinessEntityID specified is not valid';  
        RETURN 1;  
    END  
ELSE  
    BEGIN  
        PRINT 'Purchase order updated with the new employee';  
        RETURN 0;  
    END;  
GO  

另請參閱See Also

TRY...CATCH (Transact-SQL) TRY...CATCH (Transact-SQL)
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)
@@ROWCOUNT (Transact-SQL) @@ROWCOUNT (Transact-SQL)
sys.messages (Transact-SQL) sys.messages (Transact-SQL)
錯誤和事件參考 (資料庫引擎)Errors and Events Reference (Database Engine)