Send sql server job alert only when a query has rows to return
20,458
Try building a stored procedure something like below and schedule it to run as a job:
create procedure [dbo].[sp_send_merchant_email]
as
Begin
declare @recordCount int
select @recordCount = isnull(count(*), 0)
from merchand_history
where stock_code = 'zzz007' and create_timestamp >= getdate()
order by create_timestamp desc
IF (@recordCount > 0)
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YourProfile',
@recipients = '[email protected]',
@query = 'select * from merchand_history
where stock_code = ''zzz007'' and create_timestamp >= getdate()
order by create_timestamp desc' ,
@subject = 'Merchant Email ',
@Body = 'Email Merchant..... ' ,
@attach_query_result_as_file = 1 ;
End
else
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YourProfile',
@recipients = '[email protected]',
@BODY = 'No data returned ',
@subject = 'Merchant Email'
End
End;
Related videos on Youtube
![Paul D'Ambra](https://i.stack.imgur.com/ldCVf.jpg?s=256&g=1)
Author by
Paul D'Ambra
Devloper. Avoiding watermelons, asking questions, typing, semi-colons
Updated on September 17, 2022Comments
-
Paul D'Ambra almost 2 years
I have a query that checks if we've made sales of a particular stock item
select * from merchand_history where stock_code = 'zzz007' and create_timestamp >= getdate() order by create_timestamp desc
I'd like to have a sql job that emails a user (I guess using the alert mechanism) but only if there are rows returned by that query.
I can't think how to do this and submit to the hivemind. I really need a sql only solution...
-
Admin almost 14 yearsJust a quick clarification, does the email need to send the rows returned?
-
-
Paul D'Ambra almost 14 yearsperfect - cheers!
-
inlandquarter almost 14 yearsI'm glad this helped.