I have a stored procedure I'd like to run forever, but sleep for one second in a loop. When it wakes up it would poll a table to see if it should do some work. Work only needs to be done every minute, so there is no worry about the poll table getting hit with updates from two writers at the same time.
What is the best way to SLEEP()
for an interval in a stored procedure? It would be nice, actually, if it could sleep for 200 milliseconds, but one second would work too.
SQL Server's Equivalent to Sleep(): The WAITFOR Statement. In SQL Server, you can use the WAITFOR statement to delay the execution of a batch, stored procedure, or transaction. It works similar to MySQL's sleep() function.
SQL Server stored procedure for loopSQL Server does not support FOR loop. However, you can use the WHILE loop to perform the same task.
Stored routines cannot use LOAD DATA INFILE . Statements that return a result set cannot be used within a stored function. This includes SELECT statements that do not use INTO to fetch column values into variables, SHOW statements, and other statements such as EXPLAIN .
@Rachel - Yes.
I've encountered the same problem. After googling a lot, I found out that we can use
SELECT SLEEP(<seconds>);
to delay our procedures for this many seconds. In your case, using
SELECT SLEEP(0.2);
would be just fine.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With