By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,319 Members | 2,361 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,319 IT Pros & Developers. It's quick & easy.

UPDATE CURSOR and LOOP

P: n/a
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...
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.