A MySQL stored procedure example

I'm not normally a big stored procedure user or developer, but since stored procedures are supported in MySQL 5.0 and beyond I thought I'd give them a spin. In this blog post I'll show you how to create a very simple MySQL stored procedure. It's not quite a "Hello, world" stored proc, but it's close.

My sample MySQL database table

First, I start by already having a table in my MySQL database named crust_sizes that exists and is populated with some sample data. Here's what this table looks like:

mysql> desc crust_sizes;

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| crust_size  | int(11)          | NO   | UNI | NULL    |                | 
| description | varchar(20)      | NO   | UNI | NULL    |                | 
+-------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Defining the MySQL stored procedure

Next, I want to create a simple MySQL stored procedure that returns all the data in that table. Here's how I define that stored proc:

DELIMITER $$

DROP PROCEDURE IF EXISTS `pizza_store`.`GetCrustSizes` $$
CREATE PROCEDURE `GetCrustSizes`()
select * from crust_sizes $$

DELIMITER ;

That definition basically says that I want to create a stored procedure named GetCrustSizes in a database I have named pizza_store. As you can see, the query that it runs is very simple:

select * from crust_sizes

I'm not passing any parameters into this stored procedure -- at this point there's no need to make this any harder than it needs to be.

Calling/running the stored procedure

Now, I can run my MySQL stored procedure by calling it with the call command, like this:

call GetCrustSizes();

This results in the following output:

mysql> call GetCrustSizes();

+----+------------+-------------+
| id | crust_size | description |
+----+------------+-------------+
|  1 |         12 | 12 INCH     | 
|  2 |         14 | 14 INCH     | 
|  3 |         16 | 16 INCH     | 
+----+------------+-------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Pretty cool.

MySQL schema for my sample database table

In case you want to try this example on your own system I'm including the definition for my MySQL crust_sizes database table here, as well as some INSERT statements that populate the table.

drop table if exists crust_sizes;
create table crust_sizes (
  id int unsigned auto_increment not null,
  crust_size int not null unique,
  description varchar(20) not null unique,
  primary key(id),
) ENGINE=InnoDB;

insert into crust_sizes (crust_size,description) values (12, '12 INCH');
insert into crust_sizes (crust_size,description) values (14, '14 INCH');
insert into crust_sizes (crust_size,description) values (16, '16 INCH');

Just beware that I have the name of my database (pizza_store) embedded in my stored procedure definition, so you'll need to change that line to refer to your database.