Hi, I have written a stored procedure (PROC A) to generate a batch of records base on the select statement condition, and the records generated will be insert into a temporary table, so everyday we will schedule an auto email to trigger this PROC A to send out the record via emial to user. However, how could we detect whether SQL return no record from the PROC A, because we wish to skip the email trigger, if there is no record return no email should be sending out. Is there anyone have any idea, please advice. Best Rgds, Seemun
you can try the IF EXISTS clause, like this IF EXISTS ( SELECT MyCol FROM MyTable ) PRINT 'Records Found' ELSE PRINT 'No Records Found'
Hi, Another way could be to check the rowcount while inserting proc execution result into temp table, something like this:create table #t1(id int,rowname varchar(100),descr varchar(max)) insert #t1 exec yourproc_nameIF @@rowcount>0begin select * from #t1 --add email trigger logicend else begin --skip email trigger end drop table #t1
Also consider if you want to send an e-mail when no records have been sent, just to confirm that the job was indeed executed.
Hi, You have not mentioned which approach you have followed and have you considered suggestion from Adriaan i.e. to fire off an email even when no records returned to make sure the successful job execution.
