sugar Database

Parameters

Name	Type	Mode
p_tablename	varchar(100)	IN
p_key_column	varchar(100)	IN
p_parent_key_column	varchar(100)	IN
p_mode	varchar(100)	IN
p_startWith	varchar(250)	IN
p_level	varchar(100)	IN
p_fields	varchar(250)	IN
p_where_clause	varchar(250)	IN

Definition

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