SQL Server - Print Child SP Error Message

Question

I am executing a system store Proc inside a user defined stored proc, I would like help to store the error message returned by the system sp and print it from the parent sp. Below is the code of the system stored proc which is being executed from parent stored proc

EXEC msdb.dbo.sp_send_dbmail
@Anton _name = @profilename,
@recipients = @recipientslist ,
@Tomas Podoba = @tableHTML,--@tableHTML,--@bodycontent,
@Tomas Podoba _format = 'HTML',
@Gaydamak _attachments = @fileattachment,
@subject = @subjectname;

Answer 1

Did you try to put this call into BEGIN TRY / BEGIN CATCH statements?

Answer 2

Hi @Rahul Polaboina
You can use try ... catch and in catch block you can use ERROR_MESSAGE(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_STATE(), etc functions.
Try this:

DECLARE  
  @ErrorMessage   varchar(2000)  
 ,@ErrorSeverity  tinyint  
 ,@ErrorState     tinyint  
  
BEGIN TRY  
/*  Your code here  */  
END TRY  
  
BEGIN CATCH  
    SET @ErrorMessage  = ERROR_MESSAGE()  
    SET @ErrorSeverity = ERROR_SEVERITY()  
    SET @ErrorState    = ERROR_STATE()  
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)  
    BREAK  
END CATCH  

Please refer to this thread for more details.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.