How to combine CREATE PROCEDURE scripts in MySQL

Question

In MySQL, I want to write a script and put some CREATE PROCEDURE statements in it, the usual (;) wont work for some reason. Is there any other way to run multiple CREATE statements in the same script? If so, how?

Answer 1

not so much (foo.sql)

you can run this from mysql command line using \. foo.sql

use foo_db;

-- TABLES

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varbinary(32) unique not null,
created_date datetime not null
)
engine=innodb;

-- PROCEDURES

drop procedure if exists list_users;

delimiter #

create procedure list_users()
proc_main:begin
  select * from users order by username; 
end proc_main #

delimiter ;

drop procedure if exists get_user;

delimiter #

create procedure get_user
(
p_user_id int unsigned
)
proc_main:begin
    select * from users where user_id = p_user_id;
end proc_main #

delimiter ;

-- TEST DATA

insert into users (username, created_date) values
  ('f00',now()),('bar',now()),('alpha',now()),('beta',now());

-- TESTING

call list_users();

call get_user(1);

Answer 2

Before the first procedure / function declaration, define a separator like this:

DELIMITER |

After the first procedure / function declaration, close the separator: |

Do the same for each procedure / function and the whole script will work as one.

, :

DELIMITER |
 script 1 text
|

DELIMITER |
 script 2 text
|

DELIMITER |
 script 3 text
|

Answer 3

MySQL FAQ:

24.4.8: Is it possible to group stored procedures or stored functions into packages?

No. This is not supported in MySQL 5.1.

If you are not trying to create a package, you can try (temporarily) overriding the delimiter:

delimiter //