SQL Server Agent job: Run T-SQL query and then send email?

Question

We currently have a SQL Server Agent Job that has one step: to execute a stored procedure at 10AM daily that populates a SQL Server table.

Using only SSMS, I want to modify that job to do the following: send an email where the email contents will be the results of another tsql query, in comma-delimited format.

If that's not possible, just send an email once the step is complete, preferably so that I can change the email body.

I've been searching for a way to just send an email, and the only possible way was to use the Email under Notifications. But this is only a dropdown with my user in it. I can't add anyone else.

Any help is appreciated.

I can easily create a console app that does this. I do not want to go here unless it's the only way.

Answer 1

You can execute a second stored procedure that sends an email (via sp_send_dbmail), which would allow you to include the results of a query.

You could even include the mail sending as an addition to your first stored procedure, and do it all in one step.