Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a way for SLEEP() in a stored procedure?

Tags:

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.

like image 824
Don Wool Avatar asked May 12 '12 00:05

Don Wool


People also ask

How do you sleep in SQL query?

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.

Can we use for loop in stored procedure?

SQL Server stored procedure for loopSQL Server does not support FOR loop. However, you can use the WHILE loop to perform the same task.

Which function Cannot be used inside stored routines?

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 .

Can I launch a stored procedure and immediately return without waiting for it to finish?

@Rachel - Yes.


1 Answers

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.

like image 189
Roger Ray Avatar answered Sep 20 '22 07:09

Roger Ray