Table 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')

I want to write a stored procedure which will take @IdLabel as input parameter and will return the top most parent's IdLabel (definition of top most parent: the next child of root among the branches, so root will never be considered as top parent), but it is always returning the root as the parent.

Sample:

• Exec StoredProcedure 8 will return 2 as it is the next child
• Exec StoredProcedure 9 also returns 2
• Exec StoredProcedure 6 will return 3

This is the stored procedure:

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