Share via

  • Article

Using @@ERROR

The @@ERROR system function returns 0 if the last Transact-SQL statement executed successfully; if the statement generated an error, @@ERROR returns the error number. The value of @@ERROR changes on the completion of each Transact-SQL statement.

Because @@ERROR gets a new value when every Transact-SQL statement completes, process @@ERROR in one of two ways:

  • Test or use @@ERROR immediately after the Transact-SQL statement.

  • Save @@ERROR in an integer variable immediately after the Transact-SQL statement completes. The value of the variable can be used later.

If the statement generating the error is not in the TRY block of a TRY…CATCH construct, @@ERROR must be tested or used in the statement immediately after the statement that generated the error. If the statement generating the error is in a TRY block, @@ERROR can be tested or used in the first statement in the associated CATCH block. Within the scope of a CATCH block, the ERROR_NUMBER function can be used to retrieve the same error number reported by @@ERROR. ERROR_NUMBER has the advantage that it is available to all statements in the scope of the CATCH block, whereas @@ERROR is reset by the first statement in the CATCH block.

Conditional statements, such as the IF statement, reset @@ERROR. If you reference @@ERROR in an IF statement, references to @@ERROR in the IF or ELSE blocks will not retrieve the @@ERROR information. In the following example, @@ERROR is reset by IF and does not return the error number when referenced in the PRINT statement.

DECLARE @ErrorVar INT

RAISERROR(N'Message', 16, 1);
IF @@ERROR <> 0
    -- This PRINT statement prints 'Error = 0' because
    -- @@ERROR is reset in the IF statement above.
    PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8));
GO

The following example returns the expected results.

DECLARE @ErrorVar INT

RAISERROR(N'Message', 16, 1);
-- Save the error number before @@ERROR is reset by
-- the IF statement.
SET @ErrorVar = @@ERROR
IF @ErrorVar <> 0
-- This PRINT statement correctly prints 'Error = 50000'.
    PRINT N'Error = ' + CAST(@ErrorVar AS NVARCHAR(8));
GO

If you want to reference both @@ERROR and @@ROWCOUNT after a statement is run, they must be referenced in the same statement. Both @@ERROR and @@ROWCOUNT are reset with each Transact-SQL statement; therefore, both must be referenced in the same statement immediately after the one being tested. In the following example, @@ROWCOUNT will always be 0 because it is not referenced until after it has been reset by the first PRINT statement.

USE AdventureWorks;
GO
DELETE FROM HumanResources.JobCandidate
    WHERE JobCandidateID = 13;
-- This PRINT would successfully capture any error number.
PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8));
-- This PRINT will always print 'Rows Deleted = 0 because
-- the previous PRINT statement set @@ROWCOUNT to 0.
PRINT N'Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));
GO

The following example returns the expected results.

USE AdventureWorks;
GO
DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;

DELETE FROM HumanResources.JobCandidate
  WHERE JobCandidateID = 13;
-- Save @@ERROR and @@ROWCOUNT while they are both
-- still valid.
SELECT @ErrorVar = @@ERROR,
    @RowCountVar = @@ROWCOUNT;
IF (@ErrorVar <> 0)
    PRINT N'Error = ' + CAST(@ErrorVar AS NVARCHAR(8));
PRINT N'Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8));
GO

@@ERROR is raised only for errors, not for warnings; batches, stored procedures, and triggers cannot use @@ERROR to detect any warnings that have occurred.

A common use of @@ERROR in SQL Server 2000 and earlier is to indicate the success or failure of a stored procedure. An integer variable is initialized to 0. After each Transact-SQL statement completes, @@ERROR is tested for being 0, and if it is not 0, it is stored in the variable. The procedure then returns the variable on the RETURN statement. If none of the Transact-SQL statements in the procedure had an error, the variable remains at 0. If one or more statements generated an error, the variable holds the last error number. The following example shows a simple stored procedure with this logic.

USE AdventureWorks;
GO
IF EXISTS(SELECT name FROM sys.objects
          WHERE name = N'SampleProcedure')
    DROP PROCEDURE SampleProcedure;
GO
-- Create a procedure that takes one input parameter
-- and returns one output parameter and a return code.
CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT,
    @MaxVacation INT OUTPUT
AS

    -- Declare and initialize a variable to hold @@ERROR.
    DECLARE @ErrorSave1 INT, @ErrorSave2 INT;
    SET @ErrorSave1 = 0;

    -- Do a SELECT using the input parameter.
    SELECT LoginID, NationalIDNumber, Title
        FROM HumanResources.Employee
        WHERE EmployeeID = @EmployeeIDParm;

    -- Save @@ERROR value in first local variable.
    SET @ErrorSave1 = @@ERROR;

    -- Set a value in the output parameter.
    SELECT @MaxVacation = MAX(VacationHours)
        FROM HumanResources.Employee;

    -- Save @@ERROR value in second local variable. 
    SET @ErrorSave2 = @@ERROR;
    -- If second test variable contains non-zero value, 
    -- overwrite value in first local variable.
    IF (@ErrorSave2 <> 0) SET @ErrorSave1 = @ErrorSave2;

    -- Returns 0 if neither SELECT statement had
    -- an error; otherwise, returns the last error.
    RETURN @ErrorSave1;
GO
    
DECLARE @OutputParm INT;
DECLARE @ReturnCode INT;

EXEC @ReturnCode = SampleProcedure 13, @OutputParm OUTPUT;

PRINT N'OutputParm = ' + CAST(@OutputParm AS NVARCHAR(20));
PRINT N'ReturnCode = ' + CAST(@ReturnCode AS NVARCHAR(20));
GO

@@ERROR vs. TRY...CATCH

Using @@ERROR as the primary means of detecting errors leads to a very different style of error-handling code than that which is used with TRY…CATCH constructs.

  • @@ERROR must be either tested or saved after every Transact-SQL statement because a developer cannot predict in advance which statement might generate an error. This doubles the number of Transact-SQL statements that must be coded to implement a given piece of logic.

  • TRY…CATCH constructs are much simpler. A block of Transact-SQL statements is bounded by BEGIN TRY and END TRY statements, and then one CATCH block is written to handle errors that might be generated by that block of statements.

Outside of a CATCH block, @@ERROR is the only part of a Database Engine error available within the batch, stored procedure, or trigger that generated the error. All other parts of the error, such as its severity, state, and message text containing replacement strings (object names, for example) are returned only to the application in which they can be processed using the API error-handling mechanisms. If the error invokes a CATCH block, the system functions ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_NUMBER, ERROR_SEVERITY, and ERROR_STATE can be used.