Every error is recognized with a specific number and this SQL Error Number function will help us to fetch that error number, when the SQL server identifies error during the query execution. This function works within the scope of a TRY CATCH block. For example, we write series of statements inside the TRY block and if the SQL Server finds error then ERROR_NUMBER() inside the CATCH block will be executed, and returns the corresponding error.
SQL ERROR NUMBER Function Syntax
The basic syntax of the Error_Number in SQL Server is as shown below:
|
1 |
ERROR_NUMBER() |
TIP: The ERROR_NUMBER() function takes no arguments, and returns integer value as the output.
Following SQL statement will return the Error Numbers (message_id) and the corresponding text (ERROR_MESSAGE). Here, we highlighted the well know error number 8134, which is Divide by Zero error encountered
|
1 2 |
SELECT * FROM sys.messages |
OUTPUT
SQL ERROR NUMBER example
In this example we are going to show you, How to use the Error_Number() function to return the error number.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 |
BEGIN TRY DECLARE @Number tinyint, @Result tinyint; SET @Number = 252; SET @Result = @Number + 10; SELECT @Number AS Number, @Result AS Result; END TRY BEGIN CATCH PRINT N'Error Number = ' + CAST(ERROR_NUMBER() AS nvarchar(100)); END CATCH |
OUTPUT
ANALYSIS
First, we declared two tinyint variables
|
1 2 |
DECLARE @Number tinyint, @Result tinyint; |
Next we assigned 252 to Number variable and 262 to Result variable.
|
1 2 |
SET @Number = 252; SET @Result = @Number + 10; |
As we all know that tinyint holds numbers from 0 to 255 means Overflow so, it will exit from the TRY block and execute the statement inside our CATCH block, which is:
|
1 |
PRINT N'Error Number = ' + CAST(ERROR_NUMBER() AS nvarchar(100)); |
SQL ERROR NUMBER example 2
In this example we are going to find the result of 1/0.
SQL CODE
|
1 2 3 4 5 6 |
BEGIN TRY SELECT 1/0 AS Result; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber; END CATCH |
OUTPUT
ANALYSIS
Within the TRY block we are finding the result of 1/0
|
1 |
SELECT 1/0 AS Result; |
As we all know that the above statement will throw an error so, statement inside the CATCH block will be executed.
|
1 |
SELECT ERROR_NUMBER() AS ErrorNumber; |
Thank You for Visiting Our Blog
Leave a Reply