仅当查询包含要返回的行时才发送sql server作业警报

Modified on: Fri, 23 Aug 2019 17:40:03 +0800

我有一个查询,检查我们是否已销售特定库存商品

select * from merchand_history where stock_code = 'zzz007' and create_timestamp >= getdate() order by create_timestamp desc

我希望有一个sql作业通过电子邮件发送给用户(我猜是使用警报机制),但前提是该查询返回了行。

我无法想到如何做到这一点并顺从hivemind。我真的需要一个只有sql的解决方案...

最佳答案

尝试构建类似下面的存储过程并安排它作为作业运行:

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 = 'recipients@yourcompany.com',
    @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 = 'recipients@yourcompany.com', 
            @BODY = 'No data returned ', 
            @subject = 'Merchant Email'

End
End;
作者:DaniSQL

相关问答

添加新评论