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(commitcount)) || ' 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,

I can see where the first one can go into an infinite loop. When p_IDNO is not null, you will always have an infinite loop. You will keep fetching the same 5000 rows and setting the status to 'N' regardless of what the status is. And from the looks of it, it does nothing if if P_IDNO is null.

My guess is that on the second SP, you have a similar situation where the where clause is selecting the same rows over and over.

You need to add to the where clause of both: "AND STATUS <> 'N'"

Andy

Thanks for figuring out the mistake.I realised through your reply the mistake in the code which caused the loop to be infinite.