sql無限遞迴查詢

--------------所有子集資料包括自己---------------------
CREATE PROCEDURE ALLSON
@ID INT
AS
BEGIN
    WITH CTE AS
    (
        SELECT ID,PID,NAME,0 AS LVL FROM TEST1
        WHERE ID = @ID
        UNION ALL
        SELECT D.ID,D.PID,D.NAME,LVL+1 FROM CTE C INNER JOIN TEST1 D
        ON C.ID = D.PID
    )
    SELECT * FROM CTE
END


------------------所有父級資料-------------------
CREATE PROCEDURE ALLFATHER
@ID INT
AS
BEGIN
    WITH CTE AS
    (
        SELECT ID,PID,NAME,0 AS LVL FROM TEST1
        WHERE ID = @ID
        UNION ALL
        SELECT D.ID,D.PID,D.NAME,LVL+1 FROM CTE C INNER JOIN TEST1 D
        ON C.PID = D.ID
    )
    SELECT * FROM CTE
END


--FATHER
EXEC ALLFATHER 6