WAITFOR inside loop

RReynolds · #1 (**permalink**) 02-28-03, 11:17

Alright, this is dumb but it's driving me crazy!

I am trying to use the WAITFOR DELAY statement in a while loop. I want the loop to run indefinitely and perform some SQL statements periodically. I can get the WAITFOR DELAY to work fine as long as it runs only once but it will not work in the loop. Just for a simple test I wrote the following code and ran it in Query Analyzer:

WHILE 1=1 BEGIN
PRINT 'OK'
WAITFOR DELAY '000:00:05'
END

Nothing is printed and if I cancel the query it simply reports that user canceled the query, go figure. But never any output. However, if I change the delay time to '000:00:00' the loop runs and returns multiple printed rows when canceled. Is there something I'm missing here as far as resetting the delay time or something?

Paul Young · #2 (**permalink**) 02-28-03, 12:23

try this...

Code:

WHILE 1=1 BEGIN
  raiserror('OK',0,1) with nowait
  WAITFOR DELAY '000:00:05'
END

which works diffrently than...

Code:

WHILE 1=1 BEGIN
  raiserror('OK',0,1)
  WAITFOR DELAY '000:00:05'
END

As I recall the diffrence is that all output is held untill the end of the batch, adding the "with nowait" to the raiserror forced the output to the user.

RReynolds · #3 (**permalink**) 02-28-03, 14:32

Thanks Paul.

That solved my problem. I placed the "raiserror" code in the loop, along with my code, and now when I cancel the query I get the output.

Internet Services

Web Development

Digital Marketing

Consumer Tech