MySQL Recursive Procedure to Select Multiple Rows

Question

I am working on writing a recursive stored procedure in MySQL that will return multiple rows of data, namely the data for a child and all of its parents, grandparents, etc. For example, if I have the following table ("families"):

+---------+------+------------+
|  Status | Name | ParentName |
+---------+------+------------+
| healthy |    A |    NULL    |
+---------+------+------------+
| healthy |    B |    NULL    |
+---------+------+------------+
| healthy |    C |      A     |
+---------+------+------------+
| healthy |    D |      C     |
+---------+------+------------+
| healthy |    E |      B     |
+---------+------+------------+

and I run the procedure on the name 'D', it should return the following:

+---------+------+------------+
|  Status | Name | ParentName |
+---------+------+------------+
| healthy |    D |      C     |
+---------+------+------------+
| healthy |    C |      A     |
+---------+------+------------+
| healthy |    A |    NULL    |
+---------+------+------------+

For now, ordering is not very important, I should be able to figure that out later. It's also worth noting that there are cases where the parent name is alphabetically greater than the child table name and some where the parent is alphabetically less than the child table name. I've seen solutions for this that require the parent to be less than the child, but those will not work for me. This would be pretty easy if I had access to a version of MySQL that supported CTEs, but unfortunately that is not possible. Instead, I'm using stored procedures recursively to solve my problem. The code I have so far is as follows:

DELIMITER |

CREATE PROCEDURE getNextLevel( IN parent_name TEXT)

BEGIN

    IF parent_name IS NOT NULL

        SELECT Status, Name, ParentName
        FROM families
        WHERE ParentName = parent_name

        UNION

        CALL getNextLevel(ParentName);
        -- This is the line that throws an error

    END IF;

END |
DELIMITER ;

Evidently, it's not as simple as just calling my procedure recursively like that (or maybe I'm just calling it wrong). Can anyone provide any insight into what I need to do here, or possibly an alternate solution for my problem?

You can't use stored procedures like that https://stackoverflow.com/a/1492446/4104224 — Uueerdo, Jun 16 '17 at 19:54
This may be what you are looking for: https://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159. This answer is specifically performing depth based recursion from Hierarchical Data in MySQL. — Display name, Jun 16 '17 at 20:12

Uueerdo · Answer 1 · 2017-06-16T21:08:33.390

No need for recursion... pseudo-code steps

Create temp table with fields for final data you want, plus an "iteration" field
set iteration to 0
Add data for "root node" to temp table for iteration.
increment iteration
insert data into temp table from source table where parent in last iteration's inserted rows.
if rows insert, goto 4; otherwise stop iterating.
select data from temp table (excluding iteration 0, unless you want the "root node" included, and iteration column)
drop temp table

The tricky part: MySQL can be finicky about how TEMPORARY tables are used, so you may actually need two; one to accumulate the data, and another to hold the current iteration's data. You could use non-TEMPORARY tables, but then you run into the issue of concurrent calls to this procedure colliding.

MySQL Recursive Procedure to Select Multiple Rows

1 Answers1