MySQL Procedure to CREATE multiple tables is not working (WHILE loop dynamic SQL)

Question

I have added a stored procedure to create multiple tables in dynamic SQL using WHILE loop in MySQL Workbench. The query creates last table only, instead of all. I have checked with drop table to delete the if table exists. But it still show already exists. Here is the query.

CREATE DEFINER=`root`@`localhost` PROCEDURE `weeklyLooper`(IN weeklycount INT)
BEGIN

SET @count = 0;
SET @weeklylooper = weeklycount;
SET @dumpclear =  CONCAT('week' , @weeklylooper);

WHILE @count <= @weeklylooper DO
set @count = @count+1;
SET @weeklyname =  CONCAT('week' , @count);
SET @weekly =  CONCAT('total_' , @weeklyname, '_deposits');

SET @dropquery = CONCAT('DROP TABLE IF EXISTS `', @weeklyname, '`');
PREPARE droptablequery FROM @dropquery; 
EXECUTE droptablequery; 
DEALLOCATE PREPARE droptablequery; 

SET @selectquery = CONCAT('CREATE temporary TABLE ', @weeklyname ,' AS SELECT sum(deposits) As ', @weekly,'  FROM base0');

PREPARE selecttablequery FROM @selectquery; 
EXECUTE selecttablequery; 
DEALLOCATE PREPARE selecttablequery;

END WHILE;

END

Please help me to complete this.

Answer 1

You are creating temporary tables - temporary tables only exist for the extent of the session - since every exec is in a unique session AND DIFFERS from the session you are running the procedure in the temporary tables are never available to the session in which you are running the procedure...

or as the manual says 'You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed.' https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html

Consider creating permanent tables and deleting them when you are done.