存储过程递归

表
create table #t(dept varchar(5),parentdept varchar(5))
insert into #t
select 'A0401','A0400' union
select 'A0400','C0300' union
select 'C0300','B0200' union
select 'B0200','0'     union
select 'E0501','E0500' union
select 'E0500','E0101'
存储过程

CREATE proc dt_path(@dept varchar(5),@flag  int=0)   --@flag 默认值为0
as
declare @parentdept varchar(5)
if @flag=0
   create table #qq(dept varchar(5))
select @parentdept=parentdept from #t where dept=@dept
if @parentdept<>'0'
   begin
     insert into #qq select @dept
     exec dt_path @parentdept,1
   end
else
begin
     insert into #qq select @dept
  select * from #qq
end
GO

注意临时表的名称不要使用select  * from sysobjects where name='b' and type='u'



exec dt_path 'a0401'