drop table if exists batch_sql_t;
create table batch_sql_t
(
  id integer primary key auto_increment not null,
  operation varchar(33) not null,
  order_number integer not null,
  sql_statement varchar(512) not null
);

drop procedure if exists run_batch_sql;
delimiter $$
create procedure run_batch_sql(IN sql_operation varchar(22))
begin
declare finished integer;
declare dynamic_sql varchar(512);
declare sql_cursor CURSOR for select sql_statement from batch_sql_t t where operation = sql_operation order by order_number;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

open sql_cursor;
set finished = 0;
read_loop: loop
fetch sql_cursor into dynamic_sql;
if finished = 1 then
leave read_loop;
end if;

set @query := dynamic_sql;
-- select @query;

prepare prep_sql from @query;
execute prep_sql;
deallocate prepare prep_sql;

end loop read_loop;

close sql_cursor;

delete from batch_sql_t where operation = sql_operation;

end $$
delimiter ;

stored procedure for executing batch sql statements

mysql batch sql statements

mysql

SQL