Loop a select but with a delay between each try

chubnut · June 11th, 2008, 05:22 AM

This is an odd request I know but we are doing some testing on some processes and need to automate a simple set of select statements purly to hog some CPU and to see if any locks occur.

We have the following bit of example code

set nocount on
declare
@count int
begin
set @count = 1
while (@count <= 10)
    waitfor delay '00:0:03';
      select count(*) from TESTDATABASE.webtaskdetails;
      select @count = count(*) from TESTDATABASE.diary where casenam = 'VARIABLE' and status = 'P'
set @count = @count + 1
END

If we remove the "waitfor" line the code runs as required but with no 3 second delays between selects. With the "waitfor" included it seems to hang altogether. How can we incorporate the delay between selects?

Jeff Moden · June 11th, 2008, 06:26 AM

It doesn't wait forever... it runs. The problem is that it won't produce an output until the entire loop is done processing. Here's an example of how to overcome that printing problem... the details are in the comments... read 'em... :D

Code:

--===== How to force messages to screen on long running queries

--=========================================================================
--        What normally happens is there is no display until the end of run
--=========================================================================
    PRINT 1

  WAITFOR DELAY '00:00:5'
    PRINT 1.5
  WAITFOR DELAY '00:00:5'
    PRINT 2

  WAITFOR DELAY '00:00:5'
    PRINT 3
GO
--=========================================================================
--        Raiserror forces the messages to be flushed as the run proceeds
--=========================================================================
    PRINT 1
--======= Flush message 1 to client
RAISERROR ('After 1',10,1) WITH NOWAIT

  WAITFOR DELAY '00:00:5'
    PRINT 1.5
  WAITFOR DELAY '00:00:5'
    PRINT 2
--======= Flush waiting message(s) to client
       -- This will send 1.5 and 2 to the client
RAISERROR ('After 2',10,1) WITH NOWAIT

  WAITFOR DELAY '00:00:5'
    PRINT 3
--===== Flush message 3 to client
RAISERROR ('After 3',10,1) WITH NOWAIT

--Jeff Moden