Send specific message if no results available using sp_send_dbmail

Question

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 = ' ';

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?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 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

Send specific message if no results available using sp_send_dbmail

Sign up