Lotsia PLM: Форум по семейству систем PLM/PDM/TDM/ERP/Workflow

Код: Выделить всё

create procedure UnloadProject(in @obj_id numeric(18))
begin
declare local temporary table #res(
  id numeric(18),
  pos varchar(255),
  aLevel integer,
  )
declare @maxLevel integer;
declare @tree_linktype integer;

set @tree_linktype=1;
set @maxLevel=0;

insert into #res(id,pos,aLevel) values(@obj_id,'1.',@maxLevel)
while exists(select id from #res where aLevel=@maxLevel) loop
        insert into #res(id,pos)
                select tl.link_id,#res.pos  from #res join tree_link_view as tl on #res.id = tl.parent_id  
                    where #res.aLevel=@maxLevel and tl.link_type_id = @tree_linktype;
set @maxLevel=@maxLevel+1;
update #res set 
   pos=pos+right('  '+cast(number() as char),3),
   aLevel=@maxLevel
   where aLevel is null;
end loop;
select * from #res order by pos;
end