create function [dbo].[DeptTree](@initDeptCode varchar(10))
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