Home > T-SQL > SQL SERVER – Fetch the SQL error number using ERROR_NUMBER function

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

pic0

Advertisements
  1. January 6, 2015 at 12:26 am

    hello!,I love your writing so a lot! proportion we keep in touch extra approximately your article on AOL?

    I need a specialist on this house to unravel my problem.
    May be that is you! Looking ahead to look you.

  2. April 11, 2015 at 6:14 pm

    Greate post. Keep writing such kind of info on your
    page. Im really impressed by it.
    Hi there, You’ve performed an incredible job.
    I’ll definitely digg it and individually recommend to my friends.
    I am confident they will be benefited from this website.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: