Hello,
I have a store procedure using a cursor to fetch data to a parametter and then call a different store procedure. But it run with endless loop. How can I fix it?

CREATE PROCEDURE SP_TEMP
(OUT v_RETURNVALUE INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE v_IDPRODUCT INTEGER;
DECLARE v_SUB_RETURN CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE C1 CURSOR FOR SELECT IDPRODUCT FROM TBLPRODUCT ;
BEGIN

OPEN C1;
FETCH C1 INTO v_IDPRODUCT;

WHILE (SQLCODE=0) DO
CALL SP_DATA_DELETE(v_IDPRODUCT,v_SUB_RETURN );
FETCH C1 INTO v_IDPRODUCT;
END WHILE;
CLOSE C1;

END;

SET v_RETURNVALUE = 0;

END

Thank !

Your problem is using SQLCODE for your loop control. It is set after every SQL statement. Use a condition handler to set a variable and use that variable as the loop control.

Andy

Alternatively, you could use a FOR loop: FOR (cursor variant) statement (PL/SQL) - IBM DB2 9.7 for Linux, UNIX, and Windows This will avoid the separate cursor and the end-of-data handling.

Ok, thank !

That can't explain the infinite loop here: the SQLCODE tested in the WHILE condition is always the one returned by FETCH. And there are no fetches with untested SQLCODE. (Unless there are and we don't see them here in this simplified version...)