MYSQL nested loop stored procedure issue

Question

I am trying to do a simple stored procedure in mysql which has a nested loop. The idea is to check to see if the table has any values and if not then insert them. below is the code of the stored proc. I have tested all parts of the code and if i comment out the nested loop it will loop through all the values for the _my_curs_ fine. But when I place the nested loop in there it will only loop over the first value of _my_curs_ then when it completes it does not seem to get to the next value. The nested loop seems to loop over all values fine.

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;

ggiroux · Accepted Answer · 2010-02-04 18:45:07Z

6

Roland Bouman has written a nice article explaining the pitfalls and workarounds of nested cursors here: link text

answered Feb 4, 2010 at 18:45

ggiroux

6,7441 gold badge25 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mindwin Remember Monica Over a year ago

This anwswer is vulnerable to link rot. If some exerpts from the linked article are inserted here, the knowledge will be preserved in case the blog goes offline or the entry is deleted.

Neek Over a year ago

One very powerful point made in the linked article: "Sometimes you need to nest two cursors. I don't want to discuss what problems are appropriately solved with this device right now. Let me just say that most of the time someone is using this device, they are doing that because they don't know how to write a join properly." And indeed, I will now walk away and use a join instead of nested loops and my code will be far, far better than the mess I was trying to create :)

Collectives™ on Stack Overflow

MYSQL nested loop stored procedure issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related