SQL实现递归算法

样表：
child,parent
1     0
2     0
3     1
4     2
要实现这种用法一般都通过两种方式来实现：
procedure 方式：
create procedure usp_getallchild(@child int)
as
declare @t table(child int null,parent int null,level intnull)
declare @level int
set @level=0
insert into @t(child,parent,level) select child,parent,@level fromtable where child=@child
while @@rowcount>0
begin
 set @level=@level+1
 insert into @t(child,parent,level) selectchild,parent,@level from table jion @t as t on t.child=table.parentwhere t.level=@level-1
 
end
select * from @t

function 方式：
create function udf_getallchild(@child int)
returns @t_return table(child int null,parent int null,level intnull)
as
begin
 
 declare @level int
 set @level=0
 insert into @t_return(child,parent,level)select child,parent,@level from table where child=@child
 while @@rowcount>0
 begin
  set@level=@level+1
  insert into@t(child,parent,level) select child,parent,@level from table jion@t_return as t on t.child=table.parent where t.level=@level-1
  
 end
       return @t_return
end

-- =============================================
-- Author: 

      Gibil
-- Create date: 2011-9-24
--Description:   获取指定代理的所有子类方法
-- =============================================
Create FUNCTION GetAllSubAgent
(
    @AgentIDvarchar(20)
)
RETURNS @t_return TABLE(C_ID int IDENTITY (1,1) NOT NULL PRIMARYKEY,C_AgentID varchar(20) NOT NULL,C_UserName varchar(50)NULL,C_Level int NOT NULL)
AS
BEGIN
    DECLARE@Level int
    SET @Level =0
    INSERT INTO@t_return(C_AgentID,C_UserName,C_Level) SELECT@AgentID,C_UserName,@Level FROM T_UserBase AS U INNER JOIN
      T_PropertyType AS P ON U.[C_PropertyTypeID] = P.[C_TypeID] WHEREU.[C_AccountID] = @AgentID AND P.[C_UserType] = 2 --插入指定代理
    WHILE@@rowcount > 0 --上次执行的受影响行数大于0
    BEGIN
       SET @Level =@Level + 1
       INSERT INTO@t_return(C_AgentID,C_UserName,C_Level) SELECTC_AccountID,C_UserName,@Level FROM T_UserBase AS U
          INNER JOINT_PropertyType AS P ON U.[C_PropertyTypeID] = P.[C_TypeID] WHEREU.[C