Intermediate MySQL Stored Procedures

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:

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.

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

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

Example usage:

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.

At least 8 months experience at something and a lifetime of loving JCVD movies.

At least 8 months experience at something and a lifetime of loving JCVD movies.