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
There are a number of ways to do this.
- 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.
- 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).
- 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.