ALTER function [dbo].getSubDepartment)
returns @tabinfo table(deptid NUMERIC(20),deptname varchar(100),parentid numeric(20),level int)
as
begin
declare @cid int
set @cid = 1
insert @tabinfo
select deptid,deptname,parentid,@cid from DEPARTMENT where deptid = @deptid
while @@rowcount>0
begin
set @cid = @cid + 1
insert @tabinfo
select distinct ta.deptid,ta.deptname,ta.parentid,@cid
from DEPARTMENT as ta join @tabinfo as tb
on ta.parentid = tb.deptid
where tb.level = @cid-1
end
return
end