Re: While loop with Dynamic SQL

[Chris Aipperspach]

Finally got it to work thanks to Peter's suggestion on using the command line to call the sp. This gave much more info on why the sp was not working. I also had to update the first column in my select statement in the prepared sql so that it would insert the name of the table being assigned to the variable.

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_Tables 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;


call repeat_loop_proc;
select * from Test_Table_Counts;

I appreciate everyones assistance on this. I am sure there are much easier ways to do this and I'm sure I'll learn them along the way.