cursor error syntax in mysql

Question

i have such procedure but all the time i want to execute it i get syntax error near declaring variables... can anybody tell me what i am doint wrong

i have two tables one for questions and one for answers and excel file to migrate data to this tables. data in excel looks like this:

ID      N       TITLE
3       99500   question1
4       
5               answer1
6       X       answer2
7               answer3




 DELIMITER $$
create Procedure proc_answermigration()
begin

  DECLARE @i,@n,@q,@ind,@pt varchar default '';
  DECLARE @done,@aord int default 0;
  DECLARE cur cursor for select ID, N, question, from test.qustionmigration;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = 1;
  open cur;

  read_loop: Loop
  fetch cur into i,n,q;

  IF done THEN
  LEAVE read_loop;
  END IF;

  if n <> '' or n <> 'X'
    then
      select @ind = iq.question_id, @pt = iq.points from test.qustionmigration qm
      inner join ilias.qpl_questions iq on qm.question = iq.question_text
      where question = q
  else
      insert into qpl_a_sc
      (
        answer_id,
        question_fi,
        answertext,
        points,
        aorder,
        tstamp
      )
      select (select sequence from qpl_a_sc_seq),
              ind,
              question,
              pt,
              aord,
              '1342884200'
      from test.qustionmigration
      end if;

  update qpl_a_sc_seq
  set sequence = sequence + 1;

  if @aord = 0 then set @aord = 1;
  elseif @aord = 1 then set @aord = 2;
  else set @aord = 0;

  end loop;
  CLOSE cur;

end$$

DELIMITER ;

i corrected some statements but still it has syntax error saying:

#1064 - 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 'LOOP; close cur; end' at line 52


DELIMITER $$
create Procedure proc_answermigration()
begin

  DECLARE i,n,q,ind,pt varchar(500) default '';
  DECLARE done,aord,c int default 0;
  DECLARE cur cursor for select ID, N, question from test.qustionmigration;
  DECLARE  CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  open cur;


  read_loop: LOOP
    fetch cur into i,n,q;
    IF n <> '' or n <> 'X'
      then
        select ind = iq.question_id, pt = iq.points from test.qustionmigration qm
        inner join ilias.qpl_questions iq on qm.question = iq.question_text
        where question = q;
    else
        insert into qpl_a_sc
        (
          answer_id,
          question_fi,
          answertext,
          points,
          aorder,
          tstamp
        )
        select (select sequence from qpl_a_sc_seq),
                ind,
                question,
                pt,
                aord,
                '1342884200'
        from test.qustionmigration;
    end IF;

    update qpl_a_sc_seq
    set sequence = sequence + 1;

    if aord = 0 then set aord = 1;
    elseif aord = 1 then set aord = 2;
    else set aord = 0;

    set c = c + 1;


  IF done = 1 THEN
      LEAVE read_loop;
  END IF;

  END LOOP;

  close cur;
end$$
DELIMITER ;

Answer 1

Your last IF ELSE block is missing its END IF:

if @aord = 0 then set @aord = 1;
elseif @aord = 1 then set @aord = 2;
else set @aord = 0;
/* END IF either belongs here or after following statements, depending on your intended logic */
/* Either way, this block is unclosed when you close the loop */
end if

When the parser reads the END LOOP;, it is looking for the END IF, and reports a syntax error at LOOP.

Answer 2

You can't DECLARE user variables (those beginning with a @). Just SET them, or else use local variables (not beginning with a @).