ERROR_NUMBER (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

This function returns the error number of the error that caused the CATCH block of a TRY...CATCH construct to execute.

👁 Image
Transact-SQL syntax conventions

Syntax

ERROR_NUMBER ( ) 

Return Types

int

Return Value

When called in a CATCH block, ERROR_NUMBER returns the error number of the error that caused the CATCH block to run.

ERROR_NUMBER returns NULL when called outside the scope of a CATCH block.

Remarks

ERROR_NUMBER supports calls anywhere within the scope of a CATCH block.

ERROR_NUMBER returns a relevant error number regardless of how many times it runs, or where it runs within the scope of the CATCH block. This contrasts with a function like @@ERROR, which only returns an error number in the statement immediately following the one that causes an error.

In a nested CATCH block, ERROR_NUMBER returns the error number specific to the scope of the CATCH block that referenced that CATCH block. For example, the CATCH block of an outer TRY...CATCH construct could have an inner TRY...CATCH construct. Inside that inner CATCH block, ERROR_NUMBER returns the number of the error that invoked the inner CATCH block. If ERROR_NUMBER runs in the outer CATCH block, it returns the number of the error that invoked that outer CATCH block.

Examples

A. Using ERROR_NUMBER in a CATCH block

This example shows a SELECT statement that generates a divide-by-zero error. The CATCH block returns the error number.

BEGIN TRY 
 -- Generate a divide-by-zero error. 
 SELECT 1/0; 
END TRY 
BEGIN CATCH 
 SELECT ERROR_NUMBER() AS ErrorNumber; 
END CATCH; 
GO 

Here's the result set.

-----------

(0 row(s) affected)

ErrorNumber
-----------
8134

(1 row(s) affected)

B. Using ERROR_NUMBER in a CATCH block with other error-handling tools

This example shows a SELECT statement that generates a divide-by-zero error. Along with the error number, the CATCH block returns information about that error.

BEGIN TRY 
 -- Generate a divide-by-zero error. 
 SELECT 1/0; 
END TRY 
BEGIN CATCH 
 SELECT 
 ERROR_NUMBER() AS ErrorNumber, 
 ERROR_SEVERITY() AS ErrorSeverity, 
 ERROR_STATE() AS ErrorState, 
 ERROR_PROCEDURE() AS ErrorProcedure, 
 ERROR_LINE() AS ErrorLine, 
 ERROR_MESSAGE() AS ErrorMessage; 
END CATCH; 
GO 

Here's the result set.

-----------

(0 row(s) affected)

ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
----------- ------------- ----------- --------------- ---------- ----------------------------------
8134 16 1 NULL 4 Divide by zero error encountered.

(1 row(s) affected)

See Also

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