Using the FOUND_ROWS() function in a MySQL Stored Procedure.

In, Exploring the FOUND_ROWS() function in MySQL – First encounter…, I blogged about my initial exploration of the FOUND_ROWS() function. I also mentioned an alternative use for it in a Stored Procedure versus a SELECT COUNT(*) INTO statement, capturing a row count into a user-defined variable. This companion post will take the concept further, putting it into practice for a similar type of Stored Procedure…

laptop-on-desk
Photo by Fatos Bytyqi on Unsplash

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.

OS and DB used:


  • Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
  • MySQL 5.7.24

I have this defined relationship between tables book and book_genre, perhaps representing some type of personal library. Each table’s description is below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> DESC book;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| bk_id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| title    | varchar(100) | NO   |     | NULL    |                |
| genre_id | int(11)      | YES  | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> DESC book_genre;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| genre_id   | int(11)     | NO   | PRI | NULL    |       |
| genre_type | varchar(35) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

To summarize the schema: (1) Table book contains a book name (or title) and a PRIMARY KEY, bk_id; (2) Table book_genre contains all available genre’s that any book is classified as with its own PRIMARY KEY, genre_id; (3) The genre_id column in table book is a FOREIGN KEY that references back to table book_genre‘s column of the same name.

Below is a supporting query and results set, representing the above schema description:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> SELECT b.title AS book_title, bg.genre_type AS category
    -> FROM book AS b
    -> INNER JOIN book_genre AS bg
    -> USING(genre_id);
+--------------------+-------------+
| book_title          | category    |
+--------------------+-------------+
| The Lost Helm      | Fantasy     |
| God Captain        | Fantasy     |
| Shield and Savior  | Fantasy     |
| Mortar of Iron     | Fantasy     |
| No Upper Limit     | Action      |
| Golden Band        | Romance     |
| 10th Grade Blues   | Young Adult |
| Combination Locker | Young Adult |
| Blunt Spurs        | Western     |
| Loose Sadle        | Western     |
| Dark Comet Landing | Sci-Fi      |
+--------------------+-------------+
11 rows in set (0.00 sec)

With the COUNT() function and GROUP BY clause, I’ll determine how many titles of each category (genre) are on hand:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT COUNT(b.bk_id) AS num_copies, bg.genre_type AS category
    -> FROM book AS b
    -> INNER JOIN book_genre AS bg
    -> USING(genre_id)
    -> GROUP BY bg.genre_type;
+------------+-------------+
| num_copies | category    |
+------------+-------------+
|          1 | Action      |
|          4 | Fantasy     |
|          1 | Romance     |
|          1 | Sci-Fi      |
|          2 | Western     |
|          2 | Young Adult |
+------------+-------------+
6 rows in set (0.00 sec)

As you already know, the above query is a perfect candidate for a VIEW (visit Squash repeatedly typing the same query – Use a MySQL VIEW instead. I wrote for more details on MySQL Views), especially those of a daily/weekly/monthly inventory crunching nature. Yet, I want to implement something a bit more tailored and seek a single genre’s stock or count.

To accomplish that, I will lean on a Stored Procedure, CURSOR, and the FOUND_ROWS() function to determine any given genre’s count of titles on hand (as a whole genre, not an individual title).

Below are some ‘brainstorming’ thoughts off the top of my head for goals, requirements, ideas, and patterns (in no particular order):

  • The Stored Procedure should accept an IN parameter, specifying the genre to search.
  • Use a CURSOR and LOOP, moving through query results for a final count of the passed in genre’s titles.
  • Perform some sort of validation checking should a genre type be supplied in the routine call that does not exist in the fictitious book list – i.e., there is no established category for it.
  • Upon completion, provide a message indicating the number of returned results (if any) to the caller.

Now that we have a list of targeted ‘goals and requirements’, let’s implement this Stored Procedure beginning with the version below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
DELIMITER $$
CREATE
    DEFINER = CURRENT_USER
PROCEDURE copies_each_genre(p_category VARCHAR(35))
BEGIN
    DECLARE v_finish INTEGER DEFAULT 0;
    DECLARE v_book_name VARCHAR(35);
    DECLARE v_num_copies INTEGER;
    DECLARE v_cur CURSOR FOR SELECT b.title FROM book AS b INNER JOIN book_genre AS bg USING(genre_id) WHERE bg.genre_type = p_category;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finish = 1;
    IF p_category NOT IN (SELECT genre_type FROM book_genre) THEN
    SELECT 'Genre choice not available.' AS final_message;
    ELSE
        OPEN v_cur;
        how_many : LOOP
            FETCH v_cur INTO v_book_name;
            IF v_finish = 1 THEN
            LEAVE how_many;
            END IF;
        END LOOP;
        CLOSE v_cur;
        SET v_num_copies = FOUND_ROWS();
        IF v_num_copies > 1 THEN
        SELECT CONCAT_WS(' ', 'There are', v_num_copies , 'titles available to choose from in the', p_category, 'genre.') AS final_message;
        ELSE
        SELECT CONCAT_WS(' ', 'There is', v_num_copies , 'title available to choose from in the', p_category, 'genre.') AS final_message;
        END IF;
    END IF;
END $$
DELIMITER ;

Prior to discussing the above procedure, if you are curious, have a look at MySQL Stored Procedures: Create functions with error handling for repetitive tasks I wrote that is the initial ‘spark’ of creativity for this current blog post.

In that post, I used basically the same concept on a similar data set, but incorporated a SELECT COUNT(*) INTO statement to determine a specific row count based on the WHERE clause criteria. However, for this procedure, I use FOUND_ROWS() and replace that means of obtaining a count.

Let’s dissect the procedure, visiting each part in turn:

  1. Variable declarations:
    • v_finish – This variable is used in concert with the CONTINUE HANDLER located further into the declarations section of the procedure.
    • v_book_name – Used extensively with the CURSOR query and LOOP to FETCH the title, and capture a count of them (titles).
    • v_num_copies – Upon completion of the LOOP this variable is assigned the total number of rows returned from the CURSOR‘s bound query. The call to FOUND_ROWS() is assigned to v_num_copies on completion.
    • v_cur – This CURSOR is bound to the query which determines how many title of a certain genre is on hand in the ‘book list’.
    • A CONTINUE HANDLER is declared for the condition when a CURSOR‘s results set is exhausted and no more rows are available; the NOT FOUND condition. At this point, the v_finish variable controls exiting the LOOP.
  2. Immediately after all variable declarations, I first perform some validation checking that the supplied genre (through the IN parameter) is even available in the book_genre table. If not, a message is returned to the caller stating as much and the Procedure ceases execution. I felt it important to include this validation here since the follow-up statement actually opens the CURSOR for processing.
  3. Next, I open the v_cur CURSOR and retrieve any results set from its bound query into the v_book_name variable. Once a NOT FOUND condition is reached, variable v_finish is set to 1 by the CONTINUE HANDLER causing the IF v_finish = 1 conditional to be true, therefore exiting the how_many LOOP.
  4. Then, CLOSE v_cur ensures any resources associated with our cursor are not left open.
  5. Finally, the number of rows returned from the FOUND_ROWS() function is assigned to variable v_num_copies for final output processing to the caller by way of the ‘final_message’ variable.
  6. The final IF ELSE blocks report the number of available copies for the searched genre in a ‘proper’ grammar context depending if there are single or multiple copies on hand.

In past blog posts, I have used similar CURSOR‘s but the difference here is that instead of a SELECT INTO some_var type of statement, I am just using the results returned from FOUND_ROWS() to ultimately retrieve whatever count I need.

So at best, to my knowledge and understanding, using FOUND_ROWS() is limited to this type of ‘I need a count of something’ type of query/request – YMMV.

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

Explore the official MySQL 5.7 Online Manual for more information.

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.