Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Exit out of an IF statement in a stored procedure

    Hi,

    If I want to iterate through a bunch of records of a cursor and exit as soon a condition is met, what is the correct syntax for this? For eg, in this case as soon as the condition is met, I would like it to start executing the statements after CLOSE c1 ( not exit from the procedure). Thanks!


    CREATE PROCEDURE TEST()
    RESULT SETS 0
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
    BEGIN
    DECLARE c1 CURSOR FOR.....
    OPEN c1;
    FETCH records...
    -- check the value of the record.. if condition is met, close cursor, exit and go to OTHER STATEMENTS1 ---
    CLOSE c1;

    -- OTHER STATEMENTS1 ---
    -- OTHER STATEMENTS2 ---

    END;

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need two things. First you need a condition handler (NOT FOUND) to set some variable when the cursor is exhausted. Then you put you FETCH in a loop based on the variable. You then can set the variable in the IF condition to exit the loop.

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    CREATE PROCEDURE TEST()
    RESULT SETS 0
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
    BEGIN

    DECLARE at_end INT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';

    DECLARE c1 CURSOR FOR.....

    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;

    OPEN c1;

    fetch_loop:
    LOOP

    FETCH C1 INTO xxxxxxxx, xxxxxxxxxx, xxxxxxxxx;

    IF (at_end > 0 )
    THEN LEAVE fetch_loop;
    END IF;

    -- OTHER STATEMENTS WITHIN LOOP ---
    -- if possible, do a commit every n number of rows if you have updated any data
    COMMIT;

    END LOOP fetch_loop;

    CLOSE c1;

    -- OTHER STATEMENTS1 ---
    -- OTHER STATEMENTS2 ---
    -- Do a final commit if you update any data
    Commit;
    END;
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    or be a bit more direct and add that condition to the where clause and only select that record you need.
    Dave

  5. #5
    Join Date
    Apr 2009
    Posts
    42
    I believe you just need, when meet certain condition, simply execute close cursor statement, then execute following statement, that would do...

Posting Permissions

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