sql-server-2005 – 仅当查询包含要返回的行时才发送sql server

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

End
End;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!