Problem with MYSQL nested loop stored procedure

Question

I am trying to execute a simple stored procedure in mysql that has a nested loop. The idea is to check if the table has any values, and if not, insert them. below is the code of the stored procedure. I tested all parts of the code, and if I comment on the nested loop, it will post all the values ​​for the _my_curs_ parameter. But when I put a nested loop in there, it will only iterate over the first _my_curs_ value, and then when it finishes it does not seem to go to the next value. The nested loop seems to completely overlap all values.

DECLARE _my_id_ INT; DECLARE _your_id_ INT; DECLARE _found_id_ INT; DECLARE _my_curs_ CURSOR FOR SELECT my_id FROM my_ref; DECALRE _your_curs_ CURSOR FOR SELECT _your_id FROM your_ref; OPEN _my_curs_; loop_MY_CURSOR_:LOOP FETCH _my_curs_ INTO _my_id_; OPEN _your_curs_; loop_YOUR_CURSOR_:LOOP FETCH _your_curs_ INTO _your_id_; SET _found_id_ = (SELECT COUNT(id) FROM access WHERE my_id = _my_id_ AND your_id = _your_id_); IF _found_id_ = 0 THEN INSERT INTO access(my_id, your_id) VALUES(_my_id_, _your_id_); END IF; END LOOP loop_YOUR_CURSOR; CLOSE _your_curs_; END LOOP loop_MY_CURSOR; CLOSE _my_curs_; END $$ DELIMITER; 

Answer 1

Roland Buman wrote a good article explaining the pitfalls and workarounds of nested cursors: link text