Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Returns the error number of the error that caused the CATCH block of a TRY…CATCH construct to be run.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.
This function may be called anywhere within the scope of a CATCH block.
ERROR_NUMBER returns the error number regardless of how many times it is run, or where it is run within the scope of the CATCH block. This is in contrast to @@ERROR, which only returns the error number in the statement immediately after the one that causes an error, or the first statement of a CATCH block.
In nested CATCH blocks, ERROR_NUMBER returns the error number specific to the scope of the CATCH block in which it is referenced. For example, the CATCH block of an outer TRY...CATCH construct could have a nested TRY...CATCH construct. Within the nested CATCH block, ERROR_NUMBER returns the number from the error that invoked the nested CATCH block. If ERROR_NUMBER is run in the outer CATCH block, it returns the number from the error that invoked that CATCH block.
A. Using ERROR_NUMBER in a CATCH block
The following code example shows a SELECT statement that generates a divide-by-zero error. The number of the error is returned.
BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber; END CATCH; GO
B. Using ERROR_NUMBER in a CATCH block with other error-handling tools
The following code example shows a SELECT statement that generates a divide-by-zero error. Along with the error number, information that relates to the error is returned.
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