SQL：搜索节点之子子孙孙

CREATE FUNCTION [dbo].[pyx_fn_getX] ( @id INT )
RETURNS @tbl TABLE ( ID INT, Parent INT )
AS
BEGIN
  DECLARE @tb_id TABLE ( ID INT, lvls INT )
  DECLARE @lvl INT
       
  SET @lvl = 1
  INSERT  INTO @tb_id
          SELECT  @id,
                  @lvl

  WHILE EXISTS ( SELECT TOP 1
                        projectID
                 FROM   HDSSortproject
                 WHERE  Parent_projectID IN ( SELECT  ID
                                              FROM    @tb_id
                                              WHERE   lvls = @lvl ) )
    BEGIN
      SET @lvl = @lvl + 1
      INSERT  INTO @tb_id
              SELECT  projectID,
                      @lvl
              FROM    HDSSortproject
              WHERE   Parent_projectID IN ( SELECT  ID
                                            FROM    @tb_id
                                            WHERE   lvls = @lvl - 1 )
    END

  INSERT  INTO @tbl
          SELECT  projectID,
                  Parent_projectID
          FROM    HDSSortproject
          WHERE   projectID IN ( SELECT id
                                 FROM   @tb_id )
  RETURN
END