Loop over results of prepared statement query inside stored procedure

Question

I have a stored function, whose job it is to go through and analyse a lot of data from several tables. While most tables are static - and I can declare cursors to loop over the data - there are some tables that are not known in advance, specifically, language set tables (e.g. language_en, language_fr, etc.) that is, at the time the stored function is written, I do not know which of these tables will be present.

In the stored function itself, I can do this:

declare cLang cursor for
    SELECT table_name FROM information_schema.tables
    WHERE table_schema=database() and table_name like 'language_%';

declare continue handler for not found set exit_loop = true;

open cLang;
set exit_loop = false;
cLang_loop: loop
    fetch cLang into str;

    ...
end loop;

This way, I can loop over all language tables present in the database. Then I need to get the data from each of them and loop over it doing my analysis. Obviously, I can't declare a cursor for each of them, because I don't know which tables are there. I can use prepared statements though:

fetch cLang into tableName;

set @stmt_text = concat("SELECT t_code, t_string FROM ", str);
prepare stmt from @stmt_text;
execute stmt using @scId;

But now, how do I loop over the results of this query?

Answer 1

The way I ended up doing it is this. Instead of creating a dynamic cursor to query the table, I have a predefined temporary table and declare a cursor over that table. Then the dynamic sql inserts into the temp table and a regular cursor iterates over it. Something like this:

declare cLang cursor for
    SELECT table_name FROM information_schema.tables WHERE table_schema=database() and table_name like 'language_%';

declare cCode cursor for
    SELECT t_code, t_string FROM tmp_lang;

declare continue handler for not found set exit_loop = true;

open cLang;
set exit_loop = false;

cLang_loop: loop
    fetch cLang into str;
    if exit_loop then
        close cLang;
        leave cLang_loop;
    else
        create temporary table tmp_lang (t_code varchar(50), t_string varchar(2000));

        set @stmt_text = concat(
            'insert into tmp_lang (t_code, t_string) SELECT t_code, t_string
            from ', str);
        prepare stmt from @stmt_text;
        execute stmt;

        if (select count(1) from tmp_lang) > 0 then

            open cCode;

            cCode_loop: loop
                fetch cCode into lCode, lString;
                if exit_loop then
                    close cCode;
                    set exit_loop = false;
                    leave cCode_loop;
                else
                    -- now I can do whatever I need with the data
                end if;
            end loop;

        end if;

        deallocate prepare stmt;
        drop table tmp_lang;
    end if;
end loop;