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

SQL SERVER - Scope of ERROR_MESSAGE scopeerror0

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

SQL SERVER - Scope of ERROR_MESSAGE scopeerror1

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)

Solarwinds
Previous Post
SQL SERVER – Security Considerations for Contained Databases
Next Post
SQL SERVER – Finding If Date is LastDay of the Month

Related Posts

1 Comment. Leave new

Leave a Reply

Menu