SQL ERROR MESSAGE

In SQL Server, Every error is recognized with a specific error message. This SQL 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 a 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 returns 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 Divide by Zero error encountered

SELECT *
  FROM sys.messages

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

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

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:

 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

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, error_message statement inside the CATCH block (i.e., below statement) will execute.

SELECT ERROR_MESSAGE() AS [Error Message];