DRUIDRUID DRUIDRUID - 1 year ago 170
SQL Question

Send specific message if no results available using sp_send_dbmail

I would like to have a specific message sent if no results are available. Right now if my job executes and there is nothing to report the email just show up blank.

SP is below

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail_profile',
@recipients = '[email protected]',
@subject = 'Weekly',
@query = N'EXEC PTW.dbo.SP_Weekly',
@attach_query_result_as_file = 1,
@query_result_width = 150,
@query_result_header= 1,
@query_attachment_filename = 'Weekly.txt',
@importance = 'High',
@query_result_no_padding = 1,
@query_result_separator = ' ';


If there are no records available when this is ran, I would like to show
No records available at this time


I have tried to insert an
ISNULL
before
@query
but this is something I'm stumped on. Any suggestions?




Would also like the message always show
No records found for 'Yesterdays date'


Thinking id have to add
dateadd(day, datediff(day, 0, getdate()), -1)
somewhere in there but where?

Answer Source
declare @subject1  varchar(max)
declare @body1 varchar(max)
EXEC PTW.dbo.SP_Weekly
if @@rowcount=0
begin
set @subject1='No data or any message that suits you'
set @body1='some message'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail_profile',
@recipients = '[email protected]',
@subject = @subject1,
@importance = 'High',
@[email protected],
@query_result_separator = ' ';
return
end

else
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail_profile',
@recipients = '[email protected]',
@subject = 'Weekly,
@query = N'EXEC PTW.dbo.SP_Weekly',
@attach_query_result_as_file = 1,
@query_result_width = 150,
@query_result_header= 1,
@query_attachment_filename = 'Weekly.txt',
@importance = 'High',
@query_result_no_padding = 1,
@query_result_separator = ' ';
end
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download