Wednesday, 25 November 2015

SQL 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.