In SQL Server, Every error is recognized with a specific error message and this SQL Error Message function will help us to fetch that error message, 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 an error then ERROR_MESSAGE() inside the CATCH block will be executed, and returns the corresponding error description.
SQL ERROR MESSAGE Function Syntax
The basic syntax of the Error_Message in SQL Server is as shown below:
|
1 |
ERROR_MESSAGE(); |
TIP: The ERROR_MESSAGE() function takes no arguments, and returns NVARCHAR(4000) as the output.
Following SQL statement will return the Error Message (text) and the corresponding Error Number. Here, we highlighted the well know error message, which is Divide by Zero error encountered
|
1 2 |
SELECT * FROM sys.messages |
OUTPUT
SQL ERROR MESSAGE example
In this example we are going to show you, How to use the Error_Message() function to return the error message.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 |
BEGIN TRY DECLARE @Number smallint, @Result smallint; SET @Number = 32760; SET @Result = @Number + 10; SELECT @Number AS Number, @Result AS Result; END TRY BEGIN CATCH PRINT N'Error Message = ' + ERROR_MESSAGE(); END CATCH |
OUTPUT
ANALYSIS
First, we declared two smallint variables
|
1 2 |
DECLARE @Number smallint, @Result smallint; |
Next we assigned 32,760 to Number variable and 32,770 to Result variable.
|
1 2 |
SET @Number = 32760; SET @Result = @Number + 10; |
As we all know that smallint holds numbers from -32,768 to 32,767 means Overflow so, it will exit from the TRY block and execute the statement inside our CATCH block, which is:
|
1 |
PRINT N'Error Message = ' + ERROR_MESSAGE(); |
SQL ERROR MESSAGE 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_MESSAGE() AS [Error Message]; END CATCH |
OUTPUT
ANALYSIS
Within the TRY block we are finding the result of 1/0
|
1 |
SELECT 1/0 AS Result; |
We all know that, the above specified statement will throw an error so, statement inside the CATCH block (i.e., below statement) will be executed.
|
1 |
SELECT ERROR_MESSAGE() AS [Error Message]; |
Thank You for Visiting Our Blog
Leave a Reply