Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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...

share|improve this question
    
Just a quick clarification, does the email need to send the rows returned? –  Sean Howat Jul 30 '10 at 14:00
add comment

1 Answer

up vote 3 down vote accepted

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 = '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;
share|improve this answer
    
perfect - cheers! –  Paul D'Ambra Aug 2 '10 at 10:43
    
I'm glad this helped. –  DaniSQL Aug 2 '10 at 16:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.