Stored Procedures Updates

[db2user99]

Hi All,

Can anyone please help me figure out the error with these stored Procedures.I am trying to perform large updates.The Updates are being performed but it goes into an infinite loop.

CREATE PROCEDURE SAMPLE.SPROC (IN p_tablename varchar (50),
IN p_IDNO integer)
BEGIN
DECLARE SQLCODE INTEGER ;
DECLARE txt varchar (10000);
DECLARE stmt varchar (10000);
IF (p_IDNO IS NULL) THEN
SET txt =
'UPDATE (SELECT STATUS FROM ' || p_tablename || ' WHERE STATUS = ' || '''Y''' || ' FETCH FIRST 5000 ROWS ONLY ) SET STATUS = ''' || 'N''';
ELSE
SET txt =
'UPDATE (SELECT STATUS FROM ' || p_tablename || ' where IDNO = ' || char(p_IDNO)|| ' FETCH FIRST 5000 ROWS ONLY ) SET STATUS = ''' || 'N''';
PREPARE stmt FROM txt;
l :
LOOP
EXECUTE stmt;
IF SQLCODE = 100 THEN LEAVE l; END IF;
COMMIT;
END LOOP ;
END IF;
END

This stored procedure returns an error and never returns any result.
CREATE PROCEDURE SAMPLE.SPROC
(IN tabschema VARCHAR(128),
IN tabname VARCHAR(128),
IN predicate VARCHAR(1000),
IN commitcount INTEGER)
BEGIN
DECLARE SQLCODE INTEGER;
DECLARE txt VARCHAR(10000);
DECLARE stmt STATEMENT;
SET txt = 'UPDATE (SELECT 1 FROM "'
|| tabschema || '"."' || tabname || '" WHERE '
|| predicate || ' FETCH FIRST ' ||
RTRIM(CHAR(comm itcount)) || ' ROWS ONLY) SET STATUS = ''' || 'N''';
PREPARE stmt FROM txt;
l: LOOP
EXECUTE stmt;
IF SQLCODE = 100 THEN LEAVE l; END IF;
COMMIT;
END LOOP ;
END


Thanks In Advance,

[Remi]

My friend:

You have to use an Exception Handle to make that happen, cause is not enough declare SQLSTATE = 100.

Remi