CREATE PROCEDURE NYCIKCP.LIB_SEQUNCE_GEN(IN "@ADB_DIVISION" VARCHAR(32))
SPECIFIC LIB_SEQUNCE_GEN
MODIFIES SQL DATA
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE INT_COUNTER INTEGER DEFAULT 1;
DECLARE VC_P_ID VARCHAR(32) DEFAULT NULL;

DECLARE CURSOR_MAST_TABLE CURSOR FOR
--SELECT P_ID FROM LIBRA_PROCESS_MAST MAST WHERE MAST.DIVISION_ID='adv_GCS' AND MAST.ADDENDUM IS NULL ORDER BY MAST.P_NAME;
SELECT P_ID FROM LIBRA_PROCESS_MAST MAST WHERE MAST.DIVISION_ID=@ADB_DIVISION AND MAST.ADDEND_PARENT IS NULL ORDER BY MAST.P_NAME;
OPEN CURSOR_MAST_TABLE;

FETCH FROM CURSOR_MAST_TABLE INTO VC_P_ID;

WHILE(SQLSTATE = '00000') DO
UPDATE LIBRA_PROCESS_MAST MAST SET MAST.DIV_SHORT_CODE_INDEX=INT_COUNTER WHERE MAST.P_ID=VC_P_ID;
FETCH FROM CURSOR_MAST_TABLE INTO VC_P_ID;
SET INT_COUNTER=INT_COUNTER+1;
END WHILE;

CLOSE CURSOR_MAST_TABLE;

END
;

this proc when run, run for ages and does not terminate. When i debug this proc from Embarcadero DBArtisan i could detect that the while loop is causing to run the proc, as its not getting terminated even after the sqlstate changes from '00000' to '02000' when end of cursor record is reached.

I have no clue why the while loop returns true for the check SQLSTATE = '00000') when the sqlstate vale is '02000'.

i have tried replacing the while loop with if loop, i am still facing the issue. i belive the issue is not with the while or for loop but the way the check is performed in the proc.

could someone suggest the correct way to perform the check and the exit procedure for end of cursor.

refrence:
http://publib.boulder.ibm.com/infoce...c/c0024361.htm

Pretty obvious, however: Each SQL statement sets the SQLCODE and the SQLSTATE - each. If I count the number of SQL statements in the body of the loop, I see 3:

• UPDATE
• FETCH
• SET

The condition in the WHILE verifies the SQLSTATE of the last statement in the loop, i.e. the SET statement - not the FETCH.

Also, I would recommend to replace the WHILE loop with a regular FOR loop. That saves you the cursor declaration, open, close, fetch (actually 2 fetch statements), and the test whether the loop has to terminate.

Thanks Stolze. It works great with changing the flow of execution of statements. Thanks a ton.