3

Is it possible to have a stored procedure send an email after it executes successfully. I have an alert account set up but dont know how to create a trigger after the procedure completes. very new here

5

There are a number of ways to do this.

  1. Edit your SP to include a call to sp_send_dbmail upon successful completion of whatever logic you have. I would suggest nesting this into some TRY/CATCH logic. This will be the most direct route and will allow the notification(s) to occur regardless how the SP is called.
  2. Call your SP via a SQL Agent Job, and configure it to send a notification to an operator upon conclusion of the job (e.g. completion, success, or failure).
  3. Call your SP via a SQL Agent Job and include a secondary step calling sp_send_dbmail. This is helpful if there are more than one party or email distribution list you want to notify as the built in notification methods within a SQL Agent job are rather limited.
| improve this answer | |
  • How would I edit the current procedure to include this? When i right click edit it I do not see an option to add anything its only to change current parameters. – NULL.Dude Jun 19 '17 at 19:25
  • @Joe Right-Click -> Script Stored Procedure As... -> Alter To -> New Query Window Based on the question, I'm going to assume you are new to TSQL, so in this case you'll want to look up some examples and test things out before you edit your production code. There are some examples of how to use sp_send_dbmail at the bottom of the routine's definition page. Here's an article on how to call one SP from another. – John Eisbrener Jun 19 '17 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.