UPDATE CURSOR and LOOP

[Anthony Robinson]

I have a stored procedure that needs to loop through a record set,
evaluate value, then either perform an operation or move tro the next
record - depending on some criteria. Here's the procedure:

CREATE PROCEDURE AIM.UpdateArchiveRetrievalStatus ()
------------------------------------------------------------------------
-- 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 v_NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE UPDATE_CURSOR CURSOR FOR
SELECT ARCHIVERETRIEVALID FROM AIM.ARCHIVERETRIEVAL WHERE
ESTIMATEDRESPONSETIME < CURRENT TIMESTAMP --AND EXPIRATIONDATE <
DATE(CURRENT TIMESTAMP)
AND STATUSID = 2
--OR SELECT ARCHIVERETRIEVALID FROM AIM.ARCHIVERETRIEVAL WHERE
OR
ESTIMATEDRESPONSETIME < CURRENT TIMESTAMP AND EXPIRATIONDATE IS NULL
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;
END LOOP FETCH_LOOP;
CLOSE UPDATE_CURSOR;
END P1

Here's what happens: the first key is fetched into the cursor. The
proc then evaluates the number of expected images (ITEMACOUNT in
AIM.ARCHIVERETRIEVAL) with the actual number of images (count of rows
where IMAGEFRONT IS NOT NULL). If the two are equal, then all images
are there so move to te next record. If they are not equal, update the
row, then move to the next record.

I run it and it doesn't return an error. The first portion, returns
the right record set, but within the loop, the update doesn't seem to
be working?

Any ideas?

Sorry if this is a repost, forgot if I've written about this before...