MySQL Forums
Forum List  »  Stored Procedures

Re: While loop with Dynamic SQL
Posted by: Chris Aipperspach
Date: November 16, 2014 04:44PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
15502
November 14, 2014 07:21PM
2193
November 14, 2014 07:27PM
1845
November 16, 2014 12:32PM
2023
November 16, 2014 01:28PM
1525
November 16, 2014 02:55PM
1512
November 16, 2014 03:06PM
1557
November 16, 2014 05:27PM
1637
November 17, 2014 11:19PM
Re: While loop with Dynamic SQL
1716
November 16, 2014 04:44PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.