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')