While loop with Dynamic SQL

[Chris Aipperspach]

I'm switching jobs and moving over from SQL Server to mysql. I often use loops with dynamic sql to get fill rates in tables over a period of time. I started messing around this afternoon and am struggling a bit getting the following loop to execute. I did finally see on my last google search that a loop must be in a procedure so that seems to have gotten me a bit farther.

I have created two test tables below with two rows in each table. I have then placed the table names in a temp table. I'm trying to loop through the two test tables and get a count of each and insert into another table with the table names and counts.

On all the info I could find on dynamic sql it looks like I have to use CONCAT as opposed to putting the variables directly into the sql statement.

A few questions:
1. Is there an equivalent function in mysql to print the @sql string to debug so I can see what variables are in it (not that I can even get a variable to populate at this point)?
2. Can anyone please take a look at my syntax and give me a few pointers or point me in the right direction?

I hope I've given enough info and I've included all my create and insert statements.

Any help would be much appreciated.

-- Create Test_Table1
CREATE TABLE Test_Table1 (
id MEDIUMINT not null AUTO_INCREMENT,
Stuff varchar(100) not null,
primary key (ID))
engine = MyISAM;

insert into Test_Table1 (Stuff)
SELECT 'Balls'
UNION ALL
SELECT 'Frisbee'
;
-- Select * from Test_Table1

-- Create Test_Table1
CREATE TABLE Test_Table2 (
id MEDIUMINT not null AUTO_INCREMENT,
Pets varchar(100) not null,
primary key (ID))
engine = MyISAM;

insert into Test_Table2 (Pets)
SELECT 'Dogs'
UNION ALL
SELECT 'Cats';

-- Select * from Test_Table2
/*Create temp table to hold table names*/

CREATE TEMPORARY TABLE Test_Tables (
id MEDIUMINT not null AUTO_INCREMENT,
table_names varchar(100) not null,
primary key (ID))
engine = MyISAM;

insert into Test_Tables (table_names)
SELECT 'Test_Table1'
UNION ALL
SELECT 'Test_Table2'
;
-- Select * from Test_Tables



/*Create table for Table counts*/
CREATE TEMPORARY TABLE Test_Table_Counts (
Test_Table_Names varchar(100) not null,
Table_Counts int
)
engine = MyISAM;
/*Select * from Test_Table_Counts*/

/*Set @min and @max beginning values for while loop and Begin while loop*/
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `repeat_loop_proc`()
Begin
set @Min := 1;
select @max:= max(id) from Test_Tables;

while (@min <= @max)
Do
/*Set Table name from Test_Tables = to @min*/
Select @Table_names = table_names from Test_Table_Counts where id = @min;


SET @sql:=CONCAT('insert into Test_Table_Counts (Test_Table_Names,Table_Counts)
select', @Table_names, ',Count(*) from ', @Table_names);
PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;
set @min = @min + 1
end while;
end