error-transact-sql.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs	monikerRange
@@ERROR (Transact-SQL)	@@ERROR (Transact-SQL)	markingmyname	maghan	08/29/2017	sql	t-sql	reference	@@ERROR @@ERROR_TSQL	@@ERROR function errors [SQL Server], Transact-SQL error numbers [SQL Server]	TSQL	>= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current||=fabric

@@ERROR (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]

Returns the error number for the last [!INCLUDEtsql] statement executed.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

@@ERROR 

Return Types

integer

Remarks

Returns 0 if the previous [!INCLUDEtsql] 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 UPDATEHumanResources.EmployeePayHistorySET PayFrequency =4WHERE BusinessEntityID =1; IF @@ERROR =547BEGIN 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. DELETEFROMHumanResources.JobCandidateWHERE JobCandidateID = @CandidateID; -- Test the error value.  IF @@ERROR <>0BEGIN-- 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 <>0BEGIN IF @ErrorVar =547BEGIN 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 =0BEGIN 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)