@@ERROR (Transact-SQL)

Applies to: 👁 Image
SQL Server 👁 Image
Azure SQL Database 👁 Image
Azure SQL Managed Instance 👁 Image
Azure Synapse Analytics 👁 Image
Analytics Platform System (PDW) 👁 Image
SQL analytics endpoint in Microsoft Fabric 👁 Image
Warehouse in Microsoft Fabric 👁 Image
SQL database in Microsoft Fabric

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

👁 Image
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.

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).

Examples

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 AdventureWorks2022; 
GO 
UPDATE HumanResources.EmployeePayHistory 
 SET PayFrequency = 4 
 WHERE BusinessEntityID = 1; 
IF @@ERROR = 547
 BEGIN
 PRINT N'A check constraint violation occurred.';
 END
GO 

B. Using @@ERROR to conditionally exit a procedure

The following example uses IF...ELSE statements to test @@ERROR after an DELETE 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 AdventureWorks2022; 
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 AdventureWorks2022; 
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 

See Also

TRY...CATCH (Transact-SQL)
ERROR_LINE (Transact-SQL)
ERROR_MESSAGE (Transact-SQL)
ERROR_NUMBER (Transact-SQL)
ERROR_PROCEDURE (Transact-SQL)
ERROR_SEVERITY (Transact-SQL)
ERROR_STATE (Transact-SQL)
@@ROWCOUNT (Transact-SQL)
sys.messages (Transact-SQL)
Errors and Events Reference (Database Engine)