A general question about nested stored procedures:

Let's say I've got a stored procedure that selects into a cursor individual values. It then performs an update to a row in the database thast has the matching key value. The procedure loops through the values until there aren't anymore.

Here's the code:

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 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 LENGTH(IMAGEFRONT) > 0);
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;

What I am weopndering is it possible to call another stored procedure after the UPDATE statement and still have the original stored procedure loop through the vaues?

Hopefully that question made SOME sense...

Thanks!!

Anthony,
Yes it is. Be careful if the called SP terminates a Unit of Work (UOW) using either COMMIT or ROLLBACK. Read the SQL manual on DECLARE CURSOR and the WITH HOLD option.

Andy