Using a cursor in a stored procedure to loop rows MySQL

Question

Scenario: I have a stored procedure that gets data from a table based on 2 inputs: a date and a string (which is a column name). The first procedure is called from another procedure which uses a cursor to loop through rows of a table and pass each row to the string of the first procedure (column names to be checked). My input for the second procedure (which is the one to be called directly) is the date.

Issue: My first procedure is running fine when I call it on its own. My second procedure is throwing some syntax errors that I don't know how to fix.

Obs: I already check some other answers here on this topic such as Using Cursor in a Loop of a stored procedure and How can I loop through all rows of a table? (MySQL) . Actually my second procedure is now a modified version of a query I found on SE https://dba.stackexchange.com/questions/138549/mysql-loop-through-a-table-running-a-stored-procedure-on-each-entry

Issue: Currently, the code is throwing an error at line 5, in my declare of @colval.

Code:

-- Procedure for looping through rows of `wanted_columns` table:
delimiter $$
drop procedure if exists `data_check_loop` $$
create procedure `data_check_loop`(`wanted_date` date)
begin

set @dateval = `wanted_date`;
declare colval string default null;

-- boolean variable to indicate cursor is out of data
declare done tinyint default false;

-- declare a cursor to select the desired columns from the desired source table
declare cursor1
    cursor for
        select t1.c1
        from `wanted_columns` t1; 

-- catch exceptions
        declare continue handler for not found set done = true;

-- open the cursor
        open cursor1;
            my_loop: 
            loop
                fetch next from cursor1 into colval;
                if done then 
                    leave my_loop; 
                else  
                    call `set_column_stats`(colval, dateval);
                end if;
            end loop;
        close cursor1;

end $$
delimiter ;

Question: Any ideas on how to fix this?

All the `declare` statements have to come before any code e.g. `set @dateval = `wanted_date`; — Nick, Nov 27 '18 at 11:38
@Nick What about the cursor? Actually, I just moved the statements around. Same error. — DGMS89, Nov 27 '18 at 11:43
From the [manual](https://dev.mysql.com/doc/refman/8.0/en/declare.html): DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements. — Nick, Nov 27 '18 at 11:50
@Nick I just changed my set to after all the declared. Still getting the same error — DGMS89, Nov 27 '18 at 11:50

score 1 · Accepted Answer · answered Nov 27 '18 at 12:07

You have a couple of problems in your procedure. Firstly, as described in the manual:

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

So you need to move your

set @dateval = `wanted_date`;

after all the DECLAREs (including the cursor and continue handler).

Secondly, your declaration of colval is incorrect, string is not a valid data type and should be replaced with text:

declare colval text default null;

Using a cursor in a stored procedure to loop rows MySQL

1 Answers1

Linked