How to get error_message from SQL Server TRY.....CATCH block [duplicate]

Question

BEGIN TRY
    BEGIN TRANSACTION 
      --Lots of T-SQL Code here
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK
    USE  [msdb];
    EXEC sp_send_dbmail 
    @profile_name='Mail Profile',
    @recipients='[email protected]',
    @subject='Data Error',
    @body =  SELECT ERROR_MESSAGE();
END CATCH

I am getting the following error at this line

@body = SELECT ERROR_MESSAGE(); 

Incorrect syntax near the keyword 'SELECT'.

Any one know why?

Answer 1

You can not issue a SELECT statement directly into the parameter of a stored procedure. Do something like this instead:

DECLARE @err_msg AS NVARCHAR(MAX);

SET @err_msg = ERROR_MESSAGE();

EXEC sp_send_dbmail
  @profile_name='your Mail Profile here',
  @recipients='[email protected]',
  @subject='Data Error',
  @body=@err_msg