@@ERROR (Transact-SQL)
Returns the error number for the last Transact-SQL statement executed.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
@@ERROR
Return Types
integer
Remarks
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.
The SQL Server 2005 Database Engine introduces 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).
Examples
A. Use @@ERROR to detect a specific error
This example uses @@ERROR to check for a check constraint violation (error #547) in an UPDATE statement.
USE AdventureWorks;
GO
UPDATE HumanResources.Employee
SET PayFrequency = 4
WHERE NationalIDNumber = 615389812;
IF @@ERROR = 547
PRINT N'A check constraint violation occurred.';
GO
B. Use @@ERROR to conditionally exit a procedure
The IF...ELSE statements in this example 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 AdventureWorks;
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. Use @@ERROR with @@ROWCOUNT
This 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 AdventureWorks;
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,
@EmployeeID INT
AS
-- Declare variables used in error checking.
DECLARE @ErrorVar INT,
@RowCountVar INT;
-- Execute the UPDATE statement.
UPDATE PurchaseOrderHeader
SET EmployeeID = @EmployeeID
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 @EmployeeID 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 EmployeeID specified is not valid';
RETURN 1;
END
ELSE
BEGIN
PRINT 'Purchase order updated with the new employee';
RETURN 0;
END;
GO
See Also
Reference
ERROR_LINE (Transact-SQL)
ERROR_MESSAGE (Transact-SQL)
ERROR_NUMBER (Transact-SQL)
ERROR_PROCEDURE (Transact-SQL)
ERROR_SEVERITY (Transact-SQL)
ERROR_STATE (Transact-SQL)
TRY...CATCH (Transact-SQL)
@@ROWCOUNT (Transact-SQL)
SET @local\_variable (Transact-SQL)
sys.messages (Transact-SQL)
System Functions (Transact-SQL)
Other Resources
Handling Database Engine Errors
Using @@ERROR
Using TRY...CATCH in Transact-SQL