@@ERROR (Transact-SQL)@@ERROR (Transact-SQL)

08/29/2017

适用于：Applies to: SQL ServerSQL Server（所有支持的版本）SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics 并行数据仓库Parallel Data Warehouse并行数据仓库Parallel Data Warehouse适用于：Applies to: SQL ServerSQL Server（所有支持的版本）SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database Azure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics 并行数据仓库Parallel Data Warehouse并行数据仓库Parallel 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

备注

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

返回类型Return Types

integerinteger

备注Remarks

如果前一个 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 函数，但不限制该函数在语句产生错误后立即在语句中返回错误号。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 语句中检测约束检查冲突（错误 #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
    BEGIN
    PRINT N'A check constraint violation occurred.';
    END
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.

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.

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