Testing the result set of an sp_send_dbmail query?

Question

I'm trying to send an email using sp_send_dbmail in Sql Server 2005. I have both body text and a query being sent as an attachment.

Sometimes, however, the query will return an empty dataset.

Is there any way for me to test the results of the dataset before I send out the email, and if it has no results, not send it as an attachment.

I was thinking to maybe run the query before I send the email, and to test the results that way. Then, I'd have an if-else as follows:

if @@rowcount >0
  EXEC msdb.dbo.sp_send_dbmail @recipients=@recipients_list,
        @subject = @subject,
        @body = @body_text, @body_format = 'HTML',
        @query = @query,
        @attach_query_result_as_file = 1, 
        @query_result_width = 4000, 
        @query_attachment_filename = 'Details.txt'
else
EXEC msdb.dbo.sp_send_dbmail @recipients=@recipients_list,
        @subject = @subject,
        @body = @body_text, @body_format = 'HTML'

But I don't think this is an efficient way to solve the problem.

Any suggestions? TIA!!

score 1 · Accepted Answer · answered Feb 02 '11 at 17:07

Alright, I couldn't test this on my set up at work since it appears our DBAs have blocked access to this procedure. However, I know that sp_send_dbmail will not use local variables in the calling script, but it might let you use a global temp table.

This isn't a great solution, but you could try inserting your query result set into ##tempTable, then changing the query you pass to sp_send_dbmail to select * from ##tempTable if there are > 0 rows.

That should at least be better than running the original query 2x (if it works). Remember to drop it when you're done!

Testing the result set of an sp_send_dbmail query?

1 Answers1