declare cursor error on mysql "error in your SQL syntax"

Question

please help here

delimiter $
create procedure corrige_preg()
begin
    declare var_test integer;
    declare var_contador integer;
    set var_contador = 1;
    select test_tnum into var_test from preg
    group by test_tnum having max(pnum)<>count(pnum);
    if var_test is null then
        select "ok";
    else
        declare var_cursor cursor for
        select pnum from preg where test_tnum = var_test;
        open var_cursor;
        loop1: loop
            fetch var_cursor into pnum;
            pnum = var_contador;
            set var_contador=var_contador+1;
        end loop loop1;
        close var_cursor;
    end if;
end $

Error : 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 'declare var_cursor cursor for select pnum from preg where test_tnum = var_test' at line 11

Are you sure it was the same error? When I added BEGIN, I got different errors because of the undeclared variable pnum. — Barmar
– Barmar, Commented Jun 30, 2015 at 18:26

Ike Walker · Accepted Answer · 2015-06-30 18:21:28Z

1

Move the cursor declaration to the top, right after the variable declaration.

For example:

delimiter $
create procedure corrige_preg()
begin
    declare var_test integer;
    declare var_contador integer;
    declare var_pnum integer;

    declare var_cursor cursor for
    select pnum from preg where test_tnum = var_test;   

    set var_contador = 1;
    select test_tnum into var_test from preg
    group by test_tnum having max(pnum)<>count(pnum);
    if var_test is null then
        select "ok";
    else        
        open var_cursor;
        loop1: loop
            fetch var_cursor into var_pnum;
            -- var_pnum = var_contador;
            set var_contador=var_contador+1;
        end loop loop1;
        close var_cursor;
    end if;
end $

answered Jun 30, 2015 at 18:21

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

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

Comments

Barmar · Accepted Answer · 2015-06-30 18:25:32Z

Declarations have to be after BEGIN, so wrap a BEGIN/END block around the ELSE block. You also need a declaration for pnum, and have to use the SET statement to assign to it.

delimiter $
create procedure corrige_preg()
begin
    declare var_test integer;
    declare var_contador integer;
    set var_contador = 1;
    select test_tnum into var_test from preg
    group by test_tnum having max(pnum)<>count(pnum);
    if var_test is null then
        select "ok";
    else begin
        declare pnum integer;
        declare var_cursor cursor for
        select pnum from preg where test_tnum = var_test;
        open var_cursor;
        loop1: loop
            fetch var_cursor into pnum;
            set pnum = var_contador;
            set var_contador=var_contador+1;
        end loop loop1;
        close var_cursor;
    end;
    end if;
end $
delimiter ;

Collectives™ on Stack Overflow

declare cursor error on mysql "error in your SQL syntax"

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related