mysql stored procedures & dynamic tables

So you write mysql stored procedures.
Oh, and you have tables t1, t2 ... tn having almost the same structure and you wish to perform the same queries on all the tables.
So how do you go about it ?
Mysql does not allow you to change the table names dynamically.

So if you want to add index on field "F1" in all tables, then you cannot make the following piece of code working


my_loop: LOOP
select concat('t',table_no) into tablename;
alter table tablename add index idx_f1(`F1`);
set table_no = table_no+1;
if table_no>3 then
leave my_loop;
end if;
end loop my_loop;


Na-Na, the code wont work. cause you cannot have a dynamic variable for table name or field name in the sql query inside a stored procedure.

Then...

Just copy paste the following code...


my_loop: LOOP
select concat('t',table_no) into tablename;
set @sql = concat("alter table ",tablename," add index idx_f1(`F1`)");
prepare stmt from @sql;
execute stmt;
set table_no = table_no+1;
if table_no>3 then
leave my_loop;
end if;
end loop my_loop;


And bingo, the code works.
So what have we done here? We have created a sql query by concatenating different strings and variables and prepared a sql statement out of it.
And then we are executing the query.