Stored procedure to return hierarchical data

category hierarchy proc

drop procedure if exists category_hier;

delimiter #

create procedure category_hier
(
in p_cat_id smallint unsigned,
in p_name_separator varchar(3)  -- string to separate full name path segments
)
begin

declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;

create temporary table hier(
 parent_cat_id smallint unsigned, 
 cat_id smallint unsigned, 
 depth smallint unsigned default 0,
 full_name varchar(2048)
)engine = memory;

if (p_cat_id is null) then
    insert into hier select parent_cat_id, cat_id, v_depth, name from categories where parent_cat_id is NULL;
else
    insert into hier select parent_cat_id, cat_id, v_depth, name from categories where cat_id = p_cat_id;
end if;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table tmp engine=memory select * from hier;

while not v_done do

    if exists( select 1 from categories p inner join hier on p.parent_cat_id = hier.cat_id and hier.depth = v_depth) then

        insert into hier 
            select p.parent_cat_id, p.cat_id, v_depth + 1, concat(tmp.full_name,p_name_separator,p.name) from categories p 
            inner join tmp on p.parent_cat_id = tmp.cat_id and tmp.depth = v_depth;

        set v_depth = v_depth + 1;          

        truncate table tmp;
        insert into tmp select * from hier where depth = v_depth;

    else
        set v_done = 1;
    end if;

end while;

select 
 p.cat_id,
 p.name as category_name,
 b.cat_id as parent_cat_id,
 b.name as parent_category_name,
 hier.depth,
 hier.full_name
from 
 hier
inner join categories p on hier.cat_id = p.cat_id
left outer join categories b on hier.parent_cat_id = b.cat_id
order by
 hier.depth, hier.cat_id;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #

Tweak on solution provided in this SO post: http://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159