I think I may have posted about this before but not sure:

I have a procedure with a LOOP statement. It mostly works; it gets the first record it finds that matches the criteris, does the relevant update, and then that's it: it only updates one record although there are 95 records that match the search criteria.

Any advice on cleaning tyhis up would be appreciated.

Thanks in advance!

CREATE PROCEDURE AIM.UpdateArchiveRetrievalStatus (OUT COUNTER INTEGER )
------------------------------------------------------------------------
-- SQL Stored Procedure
-- April 13, 2004
-- Anthony Robinson
-- Updates Status of ArchiveRetrieval. If EstimatedResponseTime has passed,
-- retreival status is set to CompleteWithErrors
------------------------------------------------------------------------
SPECIFIC UPDATEARCRETSTS
LANGUAGE SQL
RESULT SETS 0
P1: BEGIN

DECLARE intImageCount INTEGER;
DECLARE decArchiveRetrievalID DECIMAL (13,0);
DECLARE AT_END int DEFAULT 0;
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE UPDATE_CURSOR CURSOR FOR
SELECT ARCHIVERETRIEVALID FROM AIM.ARCHIVERETRIEVAL WHERE ESTIMATEDRESPONSETIME < CURRENT TIMESTAMP
AND STATUSID = 2;

DECLARE CONTINUE HANDLER for NOT_FOUND
SET AT_END = 1;

OPEN UPDATE_CURSOR;
FETCH_LOOP:
LOOP
FETCH UPDATE_CURSOR INTO decArchiveRetrievalID;
SET intImageCount = (SELECT COUNT(IMAGEFRONT) FROM AIM.AIMRETRIEVEDITEM WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID
AND IMAGEFRONT IS NOT NULL);
IF AT_END = 1 THEN
LEAVE FETCH_LOOP;
ELSEIF intImageCount = (SELECT ITEMCOUNT FROM AIM.ARCHIVERETRIEVAL WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID) THEN
ITERATE FETCH_LOOP;
END IF;
UPDATE AIM.ArchiveRetrieval SET STATUSID = 3 WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID;
SET AT_END = AT_END + 1;
END LOOP FETCH_LOOP;
--SET COUNTER = AT_END;
CLOSE UPDATE_CURSOR;
--UPDATE AIM.ArchiveRetrieval
--SET StatusID = 3 WHERE

END P1

Choices:

a. use IF (sqlcode = 100) OR (sqlcode < 0) THEN
LEAVE fetch_loop;
END IF;
your problem appears that you are incrementing at_end after the successful process of a row. So it is hitting your if at_end = 1 then leave statement even though it fetched a 2nd successful row.

b. Or change LOOP to WHILE

OPEN cursor
FETCH
WHILE SQLCODE <> 0 DO
some code
END WHILE;