sqlserver通过存储过程遍历树

Question

表结构为 ID Name ParentID
如何通过一个存储过程 searchID （ID，level）遍历树？
其中level为层差

Answer 1

 create table createtable(
    id int,
    name varchar(100),
    parentID int 
 )
 insert into createtable(ID,Name,ParentID ) values(1,'1',0);
 insert into createtable(ID,Name,ParentID ) values(2,'1-1',1);
 insert into createtable(ID,Name,ParentID ) values(3,'1-2',1);
 insert into createtable(ID,Name,ParentID ) values(4,'1-2-1',3);
 insert into createtable(ID,Name,ParentID ) values(5,'2',0);
 insert into createtable(ID,Name,ParentID ) values(6,'2-1',0);

 create proc searchID
@ID int
as
begin
    WITH cte 
    AS
    (
        SELECT * ,0 AS level FROM createtable  WHERE ID=@ID
        UNION ALL
        SELECT g.*,level+1 FROM createtable g INNER JOIN cte 
             ON g.ParentID=cte.ID
    )
    SELECT * FROM cte
end

exec searchID '1'