mysql procedure using cursor

DELIMITER $$

CREATE PROCEDURE INSERT_NONE_HISTORY_CHECKBOX()
BEGIN
  DECLARE note_id bigint(20);

  FOR c1 IN 
    (SELECT question_id 
     FROM question_master 
     WHERE question_type LIKE '%check box%') 
  LOOP

    SELECT note_section_id INTO note_id 
    FROM answer_master 
    WHERE question_id = c1.question_id 
    LIMIT 1;

    INSERT INTO answer_master(QUESTION_ID, NOTE_SECTION_ID, ANSWER_TEXT
      , ROS_INPUT_TEXT, HAS_CHILD_QUES, MEDICATIONS_LIST_ID, STATUS_CODE)
    VALUES(c1.question_id,note_id,'none',null,0,null,1);

  END LOOP;

END $$

DELIMITER ;

i am getting error like ::

Script line: 3 You have an error in your SQL syntax; check the manual that corresponds to >your MySQL server version for the right syntax to use near 'for c1 in (select question_id >from question_master where question_type lik开发者_如何转开发e '%ch' at line 6

What am I doing wrong?

---

I don't think MySQL supports the FOR IN syntax you'll have to declare a cursor and loop using that.

DELIMITER $$

CREATE PROCEDURE INSERT_NONE_HISTORY_CHECKBOX()
BEGIN
  DECLARE note_id bigint(20);
  DECLARE Myquestion_id INTEGER;
  DECLARE done BOOLEAN DEFAULT 0; //loop variable
  DECLARE cur1 CURSOR FOR 
    SELECT question_id 
    FROM question_master 
    WHERE question_type LIKE '%check box%'; //declare the cursor
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  //stop when done.

  OPEN cur1;  //Open it.
  insert_loop: LOOP

    FETCH cur1 INTO myquestion_id;
    IF done THEN LEAVE insert_loop; END IF;
    SELECT note_section_id INTO note_id 
    FROM answer_master 
    WHERE question_id = c1.question_id 
    LIMIT 1;

    INSERT INTO answer_master(QUESTION_ID, NOTE_SECTION_ID, ANSWER_TEXT
      , ROS_INPUT_TEXT, HAS_CHILD_QUES, MEDICATIONS_LIST_ID, STATUS_CODE)
    VALUES(myquestion_id,note_id,'none',null,0,null,1);

  END LOOP;
  CLOSE cur1;

END $$

DELIMITER ;

The syntax is a bit cumbersome, but this should work.

See: http://dev.mysql.com/doc/refman/5.0/en/cursors.html