MySQL Stored Procedures and Functions enable database devs the ability to wrap useful bits of code in a nice little ‘package’ for reuse. In this blog post, I will provide a simple example to get you started on incorporating stored procedures into your own daily work flow.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

Photo by Helloquence on Unsplash
OS and DB used:
- Xubuntu Linux 16.04.3 LTS (Xenial Xerus)
- MySQL 5.7.22
What Data Do We Have?
Let’s get a description of the 3 target tables involved:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> DESC pipe; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | pipe_id | smallint(6) | NO | PRI | NULL | auto_increment | | pipe_name | varchar(25) | NO | UNI | NULL | | | joint_num | varchar(25) | NO | | NULL | | | heat | varchar(25) | NO | | NULL | | | pipe_length | decimal(4,2) | YES | | NULL | | | has_degree | tinyint(1) | NO | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) |
1 2 3 4 5 6 7 8 | mysql> DESC pw_wall_thickness; +-----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+---------+-------+ | pw_pipe_id | smallint(6) | YES | MUL | NULL | | | pw_thickness_id | smallint(6) | YES | | NULL | | +-----------------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) |
1 2 3 4 5 6 7 8 | mysql> DESC wall_thickness; +-----------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+--------------+------+-----+---------+-------+ | w_thickness_id | smallint(6) | YES | | NULL | | | wall_thickness_amount | decimal(4,3) | YES | | NULL | | +-----------------------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) |
Table wall_thickness
is of high interest, since it holds wall thickness values.
Let’s see what they are with this query:
+----------------+-----------------------+
| w_thickness_id | wall_thickness_amount |
+----------------+-----------------------+
| 129 | 0.393 |
| 130 | 0.427 |
| 133 | 0.893 |
| 138 | 0.115 |
+----------------+-----------------------+
4 rows in set (0.00 sec)
Daily Chore
Suppose we run a query similar to this one, daily, determining the shortest piece of pipe for a given wall_thickness_amount
.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> SELECT p.pipe_name, p.pipe_length -> FROM pipe AS p -> INNER JOIN pw_wall_thickness AS pw -> ON p.pipe_id = pw.pw_pipe_id -> INNER JOIN wall_thickness AS wt -> ON pw.pw_thickness_id = wt.w_thickness_id -> WHERE wt.wall_thickness_amount = 0.393 -> ORDER BY p.pipe_length ASC -> LIMIT 1; +------------+-------------+ | pipe_name | pipe_length | +------------+-------------+ | Joint-9844 | 10.40 | +------------+-------------+ 1 row in set (0.00 sec) |
That works just fine, but a request has just come to us for wall thickness value 0.721
as well.
Guess we better get to typing that 3 table JOIN
query…
Again…
Interested in JOIN
‘s in MySQL? These blog posts are for you:
- INNER JOIN in MySQL with examples.
- What’s missing? Find out using OUTER JOIN’s in MySQL with examples.
- ON vs USING: Nuances between clauses with LEFT JOIN’s – An observation.
Halt Right There!!!
Who wants to do unnecessary typing?
Let’s wrap that query in something we can reuse moving forward. Apparently, this task is becoming a common one.
Work smarter not harder right?
First Stored Procedure
Let’s use a stored procedure to take care of this task.
Here is an idea to get you going.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | DELIMITER $$ CREATE PROCEDURE proc_short_pipe(IN p_wt_amt DECIMAL(4,3), OUT p_pipe_name VARCHAR(25), OUT p_pipe_length DECIMAL(4,2)) BEGIN DECLARE v_p_name VARCHAR(25); DECLARE v_length DECIMAL(4,2); SELECT p.pipe_name, p.pipe_length INTO v_p_name, v_length FROM pipe AS p INNER JOIN pw_wall_thickness AS pw ON p.pipe_id = pw.pw_pipe_id INNER JOIN wall_thickness AS wt ON pw.pw_thickness_id = wt.w_thickness_id WHERE wt.wall_thickness_amount = p_wt_amt ORDER BY p.pipe_length ASC LIMIT 1; SELECT CONCAT(v_p_name, ' has the shortest length of ', v_length); END $$ DELIMITER ; |
Once the procedure is created, we will use SET
syntax, to initialize three session variables (I discuss session variables and SET
syntax in this blog post.) to pass as parameters to proc_short_pipe
.
1 2 | mysql> SET @wt = 0.393, @pipe_joint = ' ', @plen = 0.0; Query OK, 0 rows affected (0.00 sec) |
Then, call proc_short_pipe
, with those session variables:
1 2 3 4 5 6 7 | mysql> CALL proc_short_pipe(@wt, @pipe_joint, @plen); +------------------------------------------------------------+ | CONCAT(v_p_name, ' has the shortest length of ', v_length) | +------------------------------------------------------------+ | Joint-9844 has the shortest length of 10.40 | +------------------------------------------------------------+ 1 row in set (0.00 sec) |
That’s pretty sweet huh?
Know what’s even better?
Need to check a different wall_thickness_amount
?
Just re-set that @wt
session variable,
1 2 | mysql> SET @wt = 0.427; Query OK, 0 rows affected (0.00 sec) |
And CALL
the procedure again,
1 2 3 4 5 6 7 8 9 | mysql> CALL proc_short_pipe(@wt, @pipe_joint, @plen); +--------------------------------------------------------------------+ | CONCAT(p_pipe_name, ' has the shortest length of ', p_pipe_length) | +--------------------------------------------------------------------+ | Joint-2528 has the shortest length of 11.50 | +--------------------------------------------------------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) |
We are on to something here right?
But, if the wall thickness value is not present in the wall_thickness
table?
What then?
Let’s see.
1 2 | mysql> SET @wt = 0.111; Query OK, 0 rows affected (0.00 sec) |
1 2 3 4 5 6 7 8 9 10 11 | mysql> CALL proc_short_pipe(@wt, @pipe_joint, @plen); +--------------------------------------------------------------------+ | CONCAT(p_pipe_name, ' has the shortest length of ', p_pipe_length) | +--------------------------------------------------------------------+ | NULL | +--------------------------------------------------------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) |
NULL
is returned since 0.111
is not a present value in the wall_thickness
table.
I Want More
There is more than one way to skin a cat, so they say. Help me learn more about MySQL Stored Procedures. I’d love to hear different takes on the procedure I have included in this blog post, along with other uses you can think of.
Please share your thoughts below in the comments.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.