SQL ERROR MESSAGE

In SQL Server, every error is recognized with a specific error message. This function will help us to fetch that error message when the server identifies errors during the query execution. This function works within the scope of an SQL TRY CATCH block.

For example, we write a series of statements inside the TRY block. If the SQL Server finds an error, then ERROR_MESSAGE() inside the CATCH block will execute and return the corresponding error description.

SQL ERROR MESSAGE Function Syntax

The ERROR_MESSAGE function takes no arguments and returns NVARCHAR(4000) as the output. The syntax of the Error_Message in SQL Server is

ERROR_MESSAGE();

The following statement will return the Error Message (text) and the corresponding Error Number. Here, we highlighted the well-known SQL Server err message, which is the Divide by Zero error encountered. Please refer to the SQL ERROR NUMBER from our SQL Server programming page.

SELECT *
  FROM sys.messages
Available System Messages 0

ERROR MESSAGE example

In this example, we show you how to use the Sql Server Error_Message function to return the error message.

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
ERROR MESSAGE 2

First, we assigned 32,760 to the Number variable and 32,770 to the Result variable.

SET @Number = 32760;
SET @Result = @Number + 10;

As we all know 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:

 PRINT N'Error Message = ' + ERROR_MESSAGE();

ERROR_MESSAGE Example 2

In this example, we are going to find the result of 1/0.

BEGIN TRY
	SELECT 1/0 AS Result;
END TRY
BEGIN CATCH
	SELECT ERROR_MESSAGE() AS [Error Message];
END CATCH
ERROR MESSAGE 3

Within the TRY block, we are finding the result of 1/0

SELECT 1/0 AS Result;

We all know that the above-specified statement will throw an error. So, the error_message statement inside the CATCH block (i.e., below statement) will execute.

SELECT ERROR_MESSAGE() AS [Error Message];
Categories SQL