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.

@@ERROR is the only part of a Microsoft® SQL Server™ 2000 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 such as object names, are returned only to the application in which they can be processed using the API error handling mechanisms. Also, @@ERROR is raised only for errors, not for warnings; therefore, batches, stored procedures, and triggers do not have visibility to any warnings that may have occurred.

A common use of @@ERROR 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. This is a simple stored procedure with this logic:

USE Northwind
GO

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,
            @MaxQuantity INT OUTPUT
AS
-- Declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
SET @ErrorSave = 0

-- Do a SELECT using the input parameter.
SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @EmployeeIDParm

-- Save any non-zero @@ERROR value.
IF (@@ERROR <> 0)
    SET @ErrorSave = @@ERROR

-- Set a value in the output parameter.
SELECT @MaxQuantity = MAX(Quantity)
FROM [Order Details]

-- Save any non-zero @@ERROR value.
IF (@@ERROR <> 0)
    SET @ErrorSave = @@ERROR

-- Returns 0 if neither SELECT statement had
-- an error, otherwise returns the last error.
RETURN @ErrorSave
GO

There are situations when @@ERROR can be used with @@ROWCOUNT. In the following example, @@ERROR is used to determine if a constraint violation error occurred, and @@ROWCOUNT is used to determine the number of rows modified by the UPDATE statement, if any rows were successfully changed.

BEGIN TRAN
  UPDATE Northwind.dbo.Products
  SET UnitPrice = UnitPrice * 1.1
  WHERE CategoryID IN (1, 2, 5, 6)
  
IF @@ERROR = 547
  PRINT 'A CHECK CONSTRAINT violation occurred'
IF @@ROWCOUNT = 0 
  PRINT 'No rows updated.'
ELSE
  PRINT STR(@@ROWCOUNT) + ' rows updated.'
COMMIT -- Commits rows successfully updated.

See Also

@@ERROR

@@ROWCOUNT