Export (0) Print
Expand All

@@ERROR (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Returns the error number for the last Transact-SQL statement executed.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

Returns 0 if the previous Transact-SQL statement encountered no errors.

Returns an error number if the previous statement encountered an error. 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. You can view the text associated with an @@ERROR error number in sys.messages.

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.

Use the TRY...CATCH construct to handle errors. 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 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. For more information, see TRY...CATCH (Transact-SQL).

A. Using @@ERROR to detect a specific error

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. Using @@ERROR to conditionally exit a procedure

The following examples uses IF...ELSE statements to test @@ERROR after an INSERT statement in a stored procedure. 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. Using @@ERROR with @@ROWCOUNT

The following example uses @@ERROR with @@ROWCOUNT to validate the operation of an UPDATE statement. 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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft