Saturday, February 02, 2008

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.

2 comments:

Anonymous said...

Using this trick I was able to create a stored procedure that add dynamicaly named partitions to a table. Good job.

Anonymous said...

Hi and thanks,

What about if you want your stored procedure to update a different column, which is passed in; e.g. in the following code I want to set "update_field" dynamically.


CREATE PROCEDURE `spKRHasDuplicate`(
in v_staff_no int
,in update_field varchar(50)
,out has_duplicate smallint
)
BEGIN

SELECT v_staff_no as staff_no,
case when
(
SELECT count(staff_no) as has_duplicate
FROM temp_employees_2
WHERE staff_no = v_staff_no
group by staff_no
) > 1 then 1 else 0 end as has_duplicate;

SET has_duplicate = has_duplicate;

UPDATE temp_employees_2
SET update_field=update_field
WHERE staff_no = v_staff_no;