streetclear
New Member
I have the following code but it keeps presenting errors. The first part of the code creates the necessary table followed by - what should create stored procedures but it does not.any ideas?\[code\]drop table if exists agent;create table agent(agent_id int unsigned not null auto_increment primary key,name varchar(32) not null,commission_level tinyint unsigned default 0,parent_agent_id int unsigned default null)engine = innodb;insert into agent (name, commission_level, parent_agent_id) values('I', 99, null), ('A', 7, 1), ('B', 6, 1), ('C', 5, 2), ('D', 6, 2), ('E', 5, 3), ('F', 2, 3), ('G', 5, 5), ('H', 1, 5);delimiter ;drop procedure if exists agent_hier;delimiter #create procedure agent_hier(in p_agent_id int unsigned)proc_main:begindeclare done tinyint unsigned default 0;declare dpth smallint unsigned default 0;create temporary table hier( parent_agent_id int unsigned, agent_id int unsigned, depth smallint unsigned default 0)engine = memory;insert into hier values (p_agent_id, p_agent_id, dpth);/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */create temporary table tmp engine=memory select * from hier;while done <> 1 do if exists( select 1 from agent a inner join hier on a.parent_agent_id = hier.agent_id and hier.depth = dpth) then insert into hier select a.parent_agent_id, a.agent_id, dpth + 1 from agent a inner join tmp on a.parent_agent_id = tmp.agent_id and tmp.depth = dpth; set dpth = dpth + 1; truncate table tmp; insert into tmp select * from hier where depth = dpth; else set done = 1; end if;end while;select a.agent_id, a.name as agent_name, if(a.agent_id = b.agent_id, null, b.agent_id) as parent_agent_id, if(a.agent_id = b.agent_id, null, b.name) as parent_agent_name, hier.depth, a.commission_levelfrom hierinner join agent a on hier.agent_id = a.agent_idinner join agent b on hier.parent_agent_id = b.agent_idorder by -- dont want to sort by depth but by commision instead - i think ?? -- hier.depth, hier.agent_id; a.commission_level desc;drop temporary table if exists hier;drop temporary table if exists tmp;end proc_main #delimiter ;/*select * from agent;call agent_hier(1);call agent_hier(2);call agent_hier(3);call agent_hier(5);*/\[/code\]