November 8th, 2005, 08:33 AM
WHILE LOOP and stored procedure
hi, i have a stored procedure, pls review it:
control := 1;
cnt := 0;
WHILE control=1 LOOP
SELECT numb INTO tempNumb FROM temp;
cnt := cnt + 1;
IF tempNumb != 10 AND cnt <= 5 THEN
control := 0;
There is only one row in temp table with numb column, value=1.
The loop will run 5 times if the numb column's value not equal to 10. Each time will delay 60 seconds.
So, i run the stored procedure. When it runs 3rd loop and during delay, i change the temp table numb column to 10, and commit.
However, the stored procedure still run until 5 times and then end the loop.
My question is: Why the stored procedure cannt read the latest value from temp table, which is 10 by now?
November 11th, 2005, 05:07 AM
Does anyone try the code above? Anyone know what and why it happen?