I am try to run same query on some tables on mysql 8 by using a cursor. I can not understand what is the problem in my code

Question

I am trying to run a same query on some tables and retrieve data from them but after running the query it says 0 row affected and the result table is empty. I am using a cursor. I can not get what is the problem

delimiter //

drop procedure if exists hunt //
create procedure hunt()
begin
    DECLARE done int default false;
    DECLARE table_name CHAR(255);
    declare sqll longtext;

    DECLARE cur1 cursor for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables 
        WHERE TABLE_SCHEMA = "xyz_database" and table_name LIKE "%vendor%" ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open cur1;

    myloop: loop
        fetch next from cur1 into table_name;
        if done then
            leave myloop;
        end if;

        set sqll ='INSERT INTO Task1_AverageCapacities SELECT AVG(capacity) as AverageCapacity
FROM (SELECT cycle,MAX(mAh_transferred_during_discharging) as capacity
FROM'+table_name+' where cycle<101
GROUP BY cycle) AS avg_capacity;';

        PREPARE stmt FROM @sqll;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt ;


    end loop;

    close cur1;
end //

delimiter ;

call hunt();

Answer 1

You need to use the CONCAT() function to do string concatenation, not the + operator.

And you need a space between FROM and the table name.

set sqll = CONCAT('INSERT INTO Task1_AverageCapacities SELECT AVG(capacity) as AverageCapacity
FROM (SELECT cycle,MAX(mAh_transferred_during_discharging) as capacity
FROM ', table_name, ' where cycle<101
GROUP BY cycle) AS avg_capacity;');

And the name of the variable is sqll, not @sqll, so you prepare it with:

PREPARE stmt FROM sqll;