SQL Server – Email Results of Stored Procedure Conditionally

Within a Stored Procedure, you may want the results of a particular query emailed to you, but only if there is a result. In other words, I don’t want to be notified when the result set size is 0.

When you put a query into the “@query” of the stored procedure, the email will be sent, even if there are no results. So you need to run the query before the sp_send_dbmail runs, not during, then determine if you want to send out an email. But since sp_send_dbmail sends the query to run within another stored procedure, your temp tables are not carried into that transaction. There is, however, one simple way to allow SQL Server to see your temp table.

Global temp tables.

Using global temp tables, you can run the query, store the results into the global temp table, check if the number of results is greater than zero, and only then run the sp_send_dbmail procedure. Within the sp_send_dbmail procedure, you can select the results in the global temp table.