Sql 如何通过存储过程将 root 的下一个子 node 作为父 node

表Label(IdLabel int，IdParentLabel int，name varchar(30))

Insert Into Label Values (1, null, 'root')
Insert Into Label Values (2, 1, 'child1')
Insert Into Label Values (3, 1, 'child2')
Insert Into Label Values (4, 1, 'child3')
Insert Into Label Values (5, 2, 'grandchild1')
Insert Into Label Values (6, 3, 'grandchild2') 
Insert Into Label Values (7, 4, 'grandchild3') 
Insert Into Label Values (8, 5, 'grandgrandchild1') 
Insert Into Label Values (9, 5, 'grandgrandchild2') 

我想要编写一个存储过程，它将接受@IdLabel作为输入参数，并将返回最顶层父代的IdLabel(最顶层父代的定义:分支中根的下一个子代，因此根永远不会被视为顶层父代)，但它总是返回作为父代的根.

示例:

• Exec StoredProcedure 8将返回2，因为它是下一个子级
• Exec StoredProcedure 9也返回2
• Exec StoredProcedure 6将返回3

以下是存储过程:

CREATE PROCEDURE CheckParent
    @IdLabel int
AS
BEGIN
    DECLARE @TopParent int
    
    SELECT @TopParent = IdParentLabel
    FROM Label
    WHERE IdLabel = @IdLabel
    
    WHILE @TopParent IS NOT NULL AND @TopParent <> 1
    BEGIN
        SET @IdLabel = @TopParent
        
        SELECT @TopParent = IdParentLabel
        FROM Label
        WHERE IdLabel = @IdLabel
    END
    
    IF @TopParent IS NULL
    BEGIN
        SELECT @TopParent = IdLabel
        FROM Label
        WHERE IdParentLabel IS NULL AND IdLabel <> 1
        ORDER BY IdLabel
        OFFSET 0 ROWS
        FETCH NEXT 1 ROWS ONLY
    END
    
    SELECT @TopParent AS TopParent
END