如何从SQL Server获取error_message尝试…CATCH块[duplicate]

This question already has an answer here:

这个问题已经有了答案:

Using the result of an expression (e.g. Function call) in a stored procedure parameter list? 6 answers
使用存储过程参数列表中的表达式(例如函数调用)的结果?6答案

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='myEmail@mydomain.org',
    @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?

有人知道为什么吗?

3 个解决方案

#1

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

不能将SELECT语句直接发送到存储过程的参数中。做这样的事情:

DECLARE @err_msg AS NVARCHAR(MAX);

SET @err_msg = ERROR_MESSAGE();

EXEC sp_send_dbmail
  @profile_name='your Mail Profile here',
  @recipients='myEmail@mydomain.org',
  @subject='Data Error',
  @body=@err_msg

#2

Please Try this

请试试这个

 SET @body = ERROR_MESSAGE()

#3

See this block of code. Might help you a little bit in exception handling at Sql end.

请参见这段代码。可能会帮助您在Sql端处理异常。

BEGIN TRY
    -- RAISERROR with severity 11-19 will cause execution to 
    -- jump to the CATCH block.
    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
   -- RAISERROR (@ErrorMessage, -- Message text.
             --  @ErrorSeverity, -- Severity.
             --  @ErrorState -- State.
             --  );



EXEC sp_send_dbmail 
    @profile_name='Mail Profile',
    @recipients='myEmail@mydomain.org',
    @subject='Error Refreshing PMT Database',
    @body =  @ErrorMessage;
END CATCH;

#1