sqlServer @@ERROR 详解

@ERROR 返回执行的上一个 Transact-SQLTransact-SQL 语句的错误号。

如果前一个 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 在每一条语句执行后被清除并且重置，因此应在语句验证后立即查看它，或将其保存到一个局部变量中以备以后查看。

示例Examples

A.A. 用 @@ERROR 检测一个特定错误Using @@ERROR to detect a specific error

以下示例用 @@ERROR 在 UPDATE 语句中检测约束检查冲突（错误 #547）。The following example uses @@ERROR to check for a check constraint violation (error #547) in an UPDATE statement.

SQL复制

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

以下示例使用 IF...ELSE 语句在存储过程中测试 @@ERROR 语句后的 DELETE。The 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.

SQL复制

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. 将 @@ERROR 与 @@ROWCOUNT 一起使用Using @@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.

SQL复制

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