ERROR_MESSAGE returns the error message cause in SQL Server statement. The Scope of ERROR_MESSAGE is within catch block of try catch. If called outside the catch block it will return NULL.
Example
BEGIN TRY SELECT 'Hitesh'/5 as Value; END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH; SELECT ERROR_MESSAGE() AS ErrorMessage;
Result
ErrorMessage
Conversion failed when converting the varchar value ‘Hitesh’ to data type int.
When called outside the catch block
ErrorMessage
NULL