ERROR_NUMBER (Transact-SQL)
Returns the error number of the error that caused the CATCH block of a TRY…CATCH construct to be run.
Syntax
ERROR_NUMBER()
Return Types
int
Return Value
When called in a CATCH block, returns the error number of the error message that caused the CATCH block to be run.
Returns NULL if called outside the scope of a CATCH block.
Remarks
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.
Examples
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.
USE AdventureWorks;
GO
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.
USE AdventureWorks;
GO
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
See Also
Reference
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)
Other Resources
Retrieving Error Information in Transact-SQL
Using TRY...CATCH in Transact-SQL
Using RAISERROR
Using @@ERROR
Database Engine Error Severities