Skip to content
Advertisement

DB2 SQLPL – Unable to exit Infinite loop

I have a stored procedure created to calculate the standard deviation by hand by going through each row in Employees Table in DB2 Sample Database –

However, the procedure is stuck in an infinite loop. I am not sure as to why it is stuck as i expected the SQLSTATE to not be ‘00000’ after reading last row on table and hoped to exit. What is the problem? How do I debug? How do I fix it?

Advertisement

Answer

The SQLSTATE variable resets after each statement except GET DIAGNOSTICS. This is why every SQLSTATE check must follow FETCH immediately. There is another technique of such a loop processing based on exception handler for NOT FOUND condition + a flag variable to set there.
Below is one of possible solutions.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement