SQL SERVER – Scope of ERROR_MESSAGE

SQL Server supports Error handling efficiency. You can do this with the help of TRY and CATCH blocks. If there is an error you can get the exact error message using the system function ERROR_MESSAGE().

I will demonstrate it using the following code

BEGIN TRY
SELECT 1+'SQL' AS RESULT
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ERROR
END CATCH


The result you get is

ERROR
——————————————————————————
Conversion failed when converting the varchar value ‘SQL’ to data type int.

It is because number 1 cannot be added to a string

But ERROR_MESSAGE will work only inside the CATCH block. If you use it outside of it, you will get nothing

BEGIN TRY
SELECT 1+'SQL' AS RESULT
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ERROR
END CATCH
SELECT ERROR_MESSAGE() AS ERROR

If you execute the above, you get the following two result sets

Resultset 1:

ERROR
——————————————————————————-
Conversion failed when converting the varchar value ‘SQL’ to data type int.

Resultset 2:

ERROR
——————————————————————————-
NULL

As you see ERROR_MESSAGE() returns NULL when used outside of CATCH block.

Reference: Pinal Dave (https://blog.sqlauthority.com)