--ID aid name parentID iddegree
if OBJECT_ID('tf_getchildInfo','tf') is not null drop function tf_getchildInfo
go
create function tf_getchildInfo(@AID varchar(10))
returns @table table
(
    ID int,
    AID varchar(10),
    Name int,
    ParentID varchar(10),
    Iddegree int
)
as
Begin
    with tb1 as
    (
        --虚拟出一张表
            select 1 as id,'a1' as aid,1 as name,'' as parentID,0 as iddegree union all
            select 2,'a2',2,'a1',1 union all 
            select 3,'a3',3,'a1',1 union all 
            select 4,'a4',4,'a1',1 union all 
            select 5,'a5',5,'a2',2 union all 
            select 6,'a6',6,'a2',2 union all 
            select 7,'a7',7,'a3',2 union all 
            select 8,'a8',8,'a5',3 union all 
            select 9,'a9',9,'a8',4 
    ),
    cte as 
    (
        --使用递归功能
        select id,aid,name,parentID,iddegree from tb1
        where aid=@AID-- 使用传递的参数
        union all
        select tb1.id,tb1.aid,tb1.name,tb1.parentID,tb1.iddegree  from cte 
        join tb1 on tb1.parentID=cte.aid
    )
        --把数据放到一张变量表
    insert into @table(id,aid,name,parentID,iddegree)
    select * from cte
    return
end
go
select * from dbo.tf_getchildInfo('a1')
go
/*
ID          AID        Name        ParentID   Iddegree
----------- ---------- ----------- ---------- -----------
1           a1         1                      0
2           a2         2           a1         1
3           a3         3           a1         1
4           a4         4           a1         1
7           a7         7           a3         2
5           a5         5           a2         2
6           a6         6           a2         2
8           a8         8           a5         3
9           a9         9           a8         4

(9 row(s) affected)


*/

------解决方案--------------------

SQL code

if OBJECT_ID('v_childInfo','V') is not null drop view v_childInfo
go
create view v_childInfo
as
with tb1 as
    (
        --虚拟出一张表
            select 1 as id,'a1' as aid,1 as name,'' as parentID,0 as iddegree union all
            select 2,'a2',2,'a1',1 union all 
            select 3,'a3',3,'a1',1 union all 
            select 4,'a4',4,'a1',1 union all 
            select 5,'a5',5,'a2',2 union all 
            select 6,'a6',6,'a2',2 union all 
            select 7,'a7',7,'a3',2 union all 
            select 8,'a8',8,'a5',3 union all 
            select 9,'a9',9,'a8',4 
    ),
    cte as 
    (
        --使用递归功能
        select id,aid,name,parentID,iddegree from tb1
        where aid='a5'-- 使用传递的参数
        union all
        select tb1.id,tb1.aid,tb1.name,tb1.parentID,tb1.iddegree  from cte 
        join tb1 on tb1.parentID=cte.aid
    )
    select * from cte
go
select * from v_childInfo
go
/*
id          aid  name        parentID iddegree
----------- ---- ----------- -------- -----------
5           a5   5           a2       2
8           a8   8           a5       3
9           a9   9           a8       4

(3 row(s) affected)


*/