Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Nested Stored Procedures

    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 Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •