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;

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

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;

or be a bit more direct and add that condition to the where clause and only select that record you need.
Dave

I believe you just need, when meet certain condition, simply execute close cursor statement, then execute following statement, that would do...