Utilize parameters with MySQL Stored Procedure by examples.

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.

two peoples' hands collaborating over two laptops

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:

mysql> SELECT * FROM wall_thickness;
+----------------+-----------------------+
| 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:

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.

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.