root:BEGIN

               DECLARE _level             INT;
               DECLARE _last_row_count    INT;

               CREATE TEMPORARY TABLE IF NOT EXISTS _hierarchy_return_set (
                      _id          VARCHAR(100)
                    , _parent_id   VARCHAR(100)
                    , _level       INT
                    , INDEX(_id, _level)
                    , INDEX(_parent_id, _level)
               );

               CREATE TEMPORARY TABLE  IF NOT EXISTS _hierarchy_current_set (
                      _id          VARCHAR(100)
                    , _parent_id   VARCHAR(100)
                    , _level       INT
               );

               SET _level := 1;
               TRUNCATE TABLE _hierarchy_return_set;
               TRUNCATE TABLE _hierarchy_current_set;

               -- cleanup WHERE clause
               IF LENGTH(TRIM(p_where_clause)) = 0 THEN
                  SET p_where_clause := NULL;
               END IF;
               IF p_where_clause IS NOT NULL THEN
                  SET p_where_clause := LTRIM(p_where_clause);
                  IF UPPER(SUBSTR(p_where_clause, 1, 5)) = 'WHERE' THEN  -- remove WHERE
                     SET p_where_clause := LTRIM(SUBSTR(p_where_clause, 6));
                  END IF;
                  IF UPPER(SUBSTR(p_where_clause, 1, 4)) <> 'AND ' THEN -- Add AND
                     SET p_where_clause := CONCAT('AND ', p_where_clause);
                  END IF;
               END IF;

               -- Get StartWith records
               SET @_sql = CONCAT( 'INSERT INTO  _hierarchy_current_set( _id, _parent_id, _level ) '
                                 ,'     SELECT  ', p_key_column, ', ', p_parent_key_column, ', ', _level
                                 ,'       FROM  ', p_tablename
                                 ,'      WHERE  ', p_startWith, ' '
                                 , IFNULL( p_where_clause, '' )
                                );
               PREPARE stmt FROM @_sql;
               EXECUTE stmt;
               SET _last_row_count = ROW_COUNT();


               -- Create the statement to get the next set of data
               IF p_mode = 'D' THEN -- Down the tree

                  SET @_sql = CONCAT( 'INSERT INTO  _hierarchy_current_set'
                                     ,'            ( _id, _parent_id, _level )'
                                     ,'    SELECT  ', p_key_column, ', ', p_parent_key_column, ', ', ' @_curr_level'
                                     ,'      FROM  ', p_tableName, ' t, _hierarchy_return_set hrs '
                                     ,'     WHERE  t.', p_parent_key_column, ' = hrs._id '                -- The Parent - Child equijoin
                                     ,'       AND  hrs._level = @_last_level  '
                                     , IFNULL( p_where_clause, '' )
                                     ,';'
                                    );
                  -- SELECT 'Down Tree Insert: ', @_sql;

               ELSEIF p_mode = 'U' THEN
                  SET @_sql = CONCAT( 'INSERT INTO  _hierarchy_current_set'
                                     ,'            ( _id, _parent_id, _level )'
                                     ,'    SELECT  ', p_key_column, ', ', p_parent_key_column, ', ', ' @_curr_level'
                                     ,'      FROM  ', p_tableName, ' t, _hierarchy_return_set hrs '
                                     ,'     WHERE  t.', p_key_column, ' = hrs._parent_id '                -- The Parent - Child equijoin
                                     ,'       AND  hrs._level = @_last_level   '
                                     , IFNULL( p_where_clause, '' )
                                     ,';'
                                    );

                  -- SELECT 'Up Tree Insert: ', @_sql;

               ELSE  -- Unknown mode, abort
                  LEAVE root;
               END IF;

               PREPARE next_recs_stmt FROM @_sql;

               -- loop recursively finding parents/children
               WHILE  ( _last_row_count > 0)
               DO
                  SET _level = _level+1;

                  INSERT INTO _hierarchy_return_set
                       SELECT *
                         FROM _hierarchy_current_set;

                  TRUNCATE TABLE _hierarchy_current_set;

                  SET @_last_level := _level-1;
                  SET @_curr_level := _level;

                  EXECUTE next_recs_stmt;
                  SET _last_row_count := ROW_COUNT();

               END WHILE;

               INSERT INTO _hierarchy_return_set
                    SELECT *
                      FROM _hierarchy_current_set;

            END