Testing the result set of an sp_send_dbmail query?
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 &g开发者_如何学运维t;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!!
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!
精彩评论