SQL Server Performance

How To Detect From SQL If No Record Return

Discussion in 'Getting Started' started by seemun, Mar 4, 2008.

  1. seemun New Member

    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
  2. raoshan03 New Member

    you can try the IF EXISTS clause, like this
    IF EXISTS ( SELECT MyCol FROM MyTable )
    PRINT 'Records Found'
    ELSE
    PRINT 'No Records Found'
  3. ranjitjain New Member

    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
  4. Adriaan New Member

    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.
  5. seemun New Member

    Hi,I have follow your way and it is working successfully, thank you very much.
  6. ranjitjain New Member

    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.

Share This Page