A condition in a stored procedure that determines the email sent
I have a stored procedure that runs a query. The output of the query is emailed to a group of recipients as an attachment. The output for the query can sometimes be blank. I want to put in a condition that if there is no data, then send "No Data" in the email. Otherwise send the output as an attachment. I need help in figuring out how to do that. Thanks on advance for the help
I have a related question about the conditional email. This is my code: If EXISTS(query) SET @varCheck = 1 ELSE SET @varCheck = 0 BEGIN IF @varCheck = 1 --grabs the file from the folder and deposits it in another folder, ftp's it and sends email with the file attached EXEC msdb.dbo.sp_send_dbmail @recipients=a list @subject =' File sent', @profile_name ='IT Email', @file_attachments ='N:\folder\
File.txt' IF @varCheck = 0 EXEC msdb.dbo.sp_send_dbmail @recipients=a list @subject =' No File to Process Today', @profile_name ='IT Email' END It is working correctly if there is data in the file. But it is sending 2 emails when there is no data. What am I doing wrong? Please help
I think you misunderstood my question. I have a stored procedure that runs a bcp command to execute another stored procedure that produces a list of data as a .txt file. Another command then emails that .txt file to a list of recipients. Sometimes that list is blank. If the list is blank, then I want the email subject line to be "No Data today" otherwise send the .txt file as an attachment. How can I do this?