I have written a stored procedure that uses a cursor to select values from a table. It chooses them one at a time, evaluates the value, and then depending on whether or not criteria is met, a corresponding row is updated in a table.

When running the procedure, I'm not getting the expected results.

Could someone maybe give me a clue as to what I may be missing in my stored procedure?

Thanks in advance...

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

DECLARE intImageCount INTEGER;
DECLARE decArchiveRetrievalID DECIMAL (13,0);
DECLARE UPDATE_CURSOR CURSOR WITH HOLD FOR
SELECT ARCHIVERETRIEVALID FROM AIM.ARCHIVERETRIEVAL WHERE ESTIMATEDRESPONSETIME < CURRENT TIMESTAMP
AND STATUSID = 2;-- FOR UPDATE ;--OF AIM.ARCHIVERETRIEVAL;

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 intImageCount = (SELECT ITEMCOUNT FROM AIM.ARCHIVERETRIEVAL WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID) THEN
LEAVE FETCH_LOOP;
UPDATE AIM.ArchiveRetrieval SET STATUSID = 3 WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID;
END IF;
END LOOP;
--UPDATE AIM.ArchiveRetrieval
--SET StatusID = 3 WHERE

END P1

UPDATE AIM.ArchiveRetrieval SET STATUSID = 3 WHERE ARCHIVERETRIEVALID = decArchiveRetrievalID;

can be
UPDATE AIM.ArchiveRetrieval SET STATUSID = 3 WHERE current of UPDATE_CURSOR

I think you can achive this proc's functionality using a SQL Statement ..

Have you tried ?

Cheers
Sathyaram

I'd love to use a straight SQL statement, but my limited ability to "think out of the box" has me kinda dumbfounded.

The cursor is ther first thing I though of to itierate through the multiple records, evaluate, then update...

I tried something like a SQL statement but endedup doing a blanket update on every row that had EESTIAMTEDREPSONSETIME < CURRENT TIMSTAMP.

Any suggestions?

Thanks for the reply!!