CODEWITHSUNDEEP
mysqldatabasecursor
JLTindex
JLTindex

Reputation: 5

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

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

Upvotes: 0

Views: 2084

Answers (2)

Barmar
Barmar

Reputation: 782785

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 ;

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65587

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 $

Upvotes: 1

Related Questions