SQL---SQL recursion

sql recursion

1,use function

 

代码

Create function [dbo].[GetChild](@ID varchar(10))
returns @t table(ID varchar(10),ParentID varchar(10),Level int)
as
begin
declare @i int
set @i = 1
insert into @t select @ID,@ID,0
insert into @t select ID,ParentID,@i from Dept where ParentID = @ID

while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.ParentID,@i
from
Dept a,@t b
where
a.ParentID=b.ID and b.Level = @i-1
end
return
end

 

 

2,use the feature of sql 2005

 

 

代码

Declare @Id Int
Set @Id = 24;

With RootNodeCTE(Id,ParentId)
As
(
Select Id,ParentId From Dept Where ParentId In (@Id)
Union All
Select Dept.Id,Dept.ParentId From RootNodeCTE
Inner Join Dept
On RootNodeCTE.Id = Dept.ParentId
)

Select * From RootNodeCTE