Intermediate MySQL Stored Procedures

Peter Lafferty
Apr 27, 2017 · 3 min read

In the previous article I covered some basics of sprocs in this article I’m going to cover some loops and validation handling.

The schema looks something like this:

Basically it’s a calendar system for keeping track of sick days and holidays.

The stored procedure will cover inserting a continuous date range in to the Absence table. The basic structure will be:

CREATE PROCEDURE `addAbsence`(
IN p_startDate DATE,
IN p_endDate DATE
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
RESIGNAL;
END;

START TRANSACTION;
COMMIT;
SELECT 1 as `status`;
END

MySQL supports several flow control statements. For this sproc we’re going to use the IF statement to check the dates are a valid range. Then we will use a REPEAT statement to loop over the date range inserting in to the Absence table.

To signal a generic SQLSTATE value, use ‘45000', which means “unhandled user-defined exception.”

The condition checks the dates and if they aren’t valid it signals the server with a user defined message.

IF p_endDate < p_startDate THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'End date before start date';
END IF;

Otherwise everything is ok and proceed to the REPEAT loop which loops over each day in the range and inserts into the Absence table.

;note a variable needs to be declared for v_date
REPEAT
INSERT INTO Absence SET idAbsence = NULL, date = v_date;
SET v_date = DATE_ADD(v_date, INTERVAL 1 DAY);
UNTIL v_date > p_endDate END REPEAT;

Placing the two previous blocks of code in to the base procedure we get something like this:

CREATE PROCEDURE `addAbsence`(
IN p_startDate DATE,
IN p_endDate DATE,
IN p_idReason TINYINT
)
BEGIN
DECLARE v_date DATE DEFAULT p_startDate;
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
RESIGNAL;
END;
IF p_endDate < p_startDate THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'End date before start date';
END IF;

START TRANSACTION;
REPEAT
INSERT INTO
Absence
SET
idAbsence = NULL,
date = v_date,
idReason = p_idReason;
SET v_date = DATE_ADD(v_date, INTERVAL 1 DAY);
UNTIL v_date > p_endDate END REPEAT;
COMMIT;
SELECT 1 as `status`;
END

Example usage:

mysql> use calendar
Database changed
mysql> insert into Reason SET reason = 'Sick';
Query OK, 1 row affected (0.00 sec)
mysql> insert into Reason SET reason = 'Absence';
Query OK, 1 row affected (0.00 sec)
mysql> CALL addAbsence('2017-05-01', '2017-04-29', 1);
ERROR 1644 (45000): End date before start date
mysql> CALL addAbsence('2017-04-01', '2017-04-29', 1);
+--------+
| status |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)mysql> CALL addAbsence('2017-04-01', '2017-04-29', 10);
ERROR 1452 (23000): Cannot add **SNIP**
mysql> select count(*) from Absence;
+----------+
| count(*) |
+----------+
| 29 |
+----------+
1 row in set (0.00 sec)

That covers off intermediate SPROC usage. With basic validation handling and loops. Couple those with the ITERATE and LEAVE statements and a wide range of SPROCs can be written.


Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store