DB2 SQLPL - Unable to exit Infinite loop

Question

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?

score 2 · Accepted Answer · answered Mar 01 '20 at 08:31

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.

OPEN cursor1;
FETCH FROM cursor1 INTO TEMP;
WHILE(SQLSTATE = '00000')
       DO
           SET SUM_SALARY = SUM_SALARY + TEMP;
           SET SUM_SALARY_SQUARED = SUM_SALARY_SQUARED + (TEMP * TEMP);
           SET NUM_ROWS = NUM_ROWS + 1;
           FETCH FROM cursor1 INTO TEMP;
       END WHILE;

DB2 SQLPL - Unable to exit Infinite loop

1 Answers1