可以递归,亦可循环
----
例如
--返回按层次分级的部门列表
CREATE PROCEDURE dbo.Get_Depart_Tree
AS
declare @level int
declare @t table(id_dept int,parid int,deptname varchar(20),level int)
set @level = 1
insert into @t select id_dept,parid,deptname,@level from sys_deptment where parid = 0
while(@@rowcount>0)
begin
set @level=@level+1
insert into @t
select a.id_dept,a.parid,a.deptname,@level from sys_deptment a
join @t b on a.parid = b.id_dept where b.level=@level-1
end
select id_dept,parid,deptname,level from @t
RETURN