SQLµÝ¹é²éѯ²¿ÃÅ

create function [dbo].[DeptTree](@initDeptCode  varchar(10))/*¶¨Ò庯Êýc_tree£¬ÊäÈë²ÎÊýΪ³õʼ½ÚµãDeptCode*/
returns @t table(DeptCode varchar(10),UpDeptCode  varchar(10),lev int)/*¶¨Òå±ítÓÃÀ´´æ·ÅÈ¡³öµÄÊý¾Ý*/
begin
  declare @i int/*±êÖ¾µÝ¹é¼¶±ð*/
  set @i=1
  insert @t select DeptCode,UpDeptCode,@i from vi_dept where DeptCode=@initDeptCode
  while @@rowcount<>0
  begin
  set @i=@i+1
  insert @t select a.DeptCode,a.UpDeptCode,@i from vi_dept as a,@t as b
 where b.DeptCode=a.UpDeptCode and b.lev=@i-1
  end
return
end
 select * from dbo.DeptTree('0000')