Creating MySQL stored procedures using MySQL

Question

Is there a way to programmatically create stored procedures using MySQL? I can write a stored procedure to create databases and tables using prepared statements, but I get an error message saying it is not supported to create stored procedures with prepared statements.

I realize I can do this in PHP or Java, but so far I have been keeping all my schema management tools as SQL scripts, and I would like to avoid a second language dependency if I can.

Answer 1

I think you can do it by inserting a record into INFORMATION_SCHEMA.ROUTINES table. I haven't tried it, but it should work (one day in the past I forgot to add --routines switch to mysqldump, and later I restored all procedures by dumping this one table).

Answer 2

One method you can try is to build the create procedure statement dynamically in SQL, then use select into outfile to dump the statement to local disk, and then source the file to load the procedure into the DB.

Here's a quick example:

set @proc_name = 'my_proc';
set @body1 = 'select ''hello''; ';
set @body2 = 'select ''world''; ';
set @delimiter = '$$';

set @create_proc_stmt = concat(
  'create procedure ',
  @proc_name,
  '() begin ', 
  @body1,
  @body2, 
  ' end ',
  @delimiter
);

select @create_proc_stmt into outfile '/tmp/create_proc_stmt.sql';

delimiter $$
\. /tmp/create_proc_stmt.sql
delimiter ;

call my_proc();