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 = 'myemail@somename.com',
@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 = ' ';
No records available at this time
ISNULL
@query
No records found for 'Yesterdays date'
dateadd(day, datediff(day, 0, getdate()), -1)
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 = 'myemail@somename.com',
@subject = @subject1,
@importance = 'High',
@body=@body1,
@query_result_separator = ' ';
return
end
else
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail_profile',
@recipients = 'myemail@somename.com',
@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