SQL SERVER – Fetch the SQL error number using ERROR_NUMBER function

ERROR_NUMBER() 

This function is used to fetch the SQL error number when the SQL Server throws during query execution. It gives the same error number as @@ERROR does but ERROR_NUMBER() function works within the try..catch scope. During the execution we can get different error number in each try…block because of scope boundaries. But, @ERROR gives us the last generated error number though out the program.

It returns an integer value and if this function is called outside the try…catch block then it returns NULL.

Syntax

ERROR_NUMBER()

Example

1. This simple example is to print the error number and the similar message from the system view sys.messages.

SQL Statement

BEGIN TRY
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

SELECT * FROM sys.messages WHERE message_id = 8134 AND language_id = 1033
GO

Output