SQL: Find nearest enabled parent in a hierarchy using T-SQL

I was recently answering a forum question about working with a hierarchy. The poster had a table of nodes but importantly, nodes could be disabled. If that happened, the children of the node would move up to their nearest parent that wasn’t disabled.

That is, if their parent was disabled and their grandparent was also disabled, then their great-grandparent would become their effective parent.

I provided the following code example, and I hope it helps someone.

USE tempdb;
GO

DROP FUNCTION IF EXISTS dbo.GetParentNode;
GO

CREATE FUNCTION dbo.GetParentNode
( 
    @NodePath varchar(max)
)
RETURNS int
AS 
BEGIN
    /*
        SELECT dbo.GetParentNode('12/13/14');
        SELECT dbo.GetParentNode('12/14');
    */

    DECLARE @ReturnValue int;
    DECLARE @StringToProcess varchar(max) = REVERSE(@NodePath);
    DECLARE @DelimiterLocation int;

    SET @DelimiterLocation = CHARINDEX('/', @StringToProcess);

    IF @DelimiterLocation > 0
    BEGIN
        SET @StringToProcess 
           = SUBSTRING(@StringToProcess, @DelimiterLocation + 1, 
                       LEN(@StringToProcess));
        SET @DelimiterLocation = CHARINDEX('/', @StringToProcess);

        IF @DelimiterLocation = 0
        BEGIN
            SET @ReturnValue = CAST(REVERSE(@StringToProcess) AS int);
        END ELSE BEGIN
            SET @ReturnValue 
              = CAST(REVERSE(LEFT(@StringToProcess, @DelimiterLocation - 1)) 
                AS int);
        END;
    END;

    RETURN @ReturnValue;   
END;
GO

DROP TABLE IF EXISTS dbo.Nodes;
GO

CREATE TABLE dbo.Nodes
(
     NodeID int, 
     ParentNodeID int, 
     NodeName nvarchar(20),   
     IsDisabled bit
);

INSERT dbo.Nodes 
(
    NodeID, ParentNodeID, NodeName, IsDisabled
)
VALUES (1, NULL, 'node-1', 0),
       (2, 1, 'node-2', 1),
       (3, 1, 'node-3', 0),
       (4, 2, 'node-4', 0),
       (5, 2, 'node-5', 0),
       (6, 3, 'node-6', 0),
       (7, 4, 'node-6', 0),
       (8, 4, 'node-6', 0);

WITH AllNodes AS
(
    SELECT toplevel.NodeID, 
           toplevel.IsDisabled,
           toplevel.ParentNodeID, 
           1 AS NodeLevel, 
           toplevel.NodeName, 
           CAST(toplevel.NodeID AS varchar(max)) AS NodePath
    FROM dbo.Nodes AS toplevel 
    WHERE toplevel.NodeID = 1

    UNION ALL

    SELECT 
        n.NodeID, 
        n.IsDisabled, 
        n.ParentNodeID, 
        an.NodeLevel + 1, 
        n.NodeName,
        an.NodePath + CASE WHEN n.IsDisabled = 0 
                           THEN '/' + CAST(n.NodeID AS varchar(max))
                           ELSE ''
                      END
    FROM dbo.Nodes AS n
    INNER JOIN AllNodes AS an 
    ON an.NodeID = n.ParentNodeID
)
SELECT an.NodeID, an.IsDisabled, an.NodeName,
       an.ParentNodeID, an.NodeLevel, an.NodePath,
       dbo.GetParentNode(an.NodePath) AS TrueParentNodeID
FROM AllNodes AS an
WHERE an.IsDisabled = 0;

DROP TABLE dbo.Nodes;
GO

The approach I took was to use a recursive CTE to traverse the nodes. As I passed through each node level, I’d add to a NodePath only if the node wasn’t disabled. That way, I built up a NodePath of just the active nodes leading to the particular node.

To make it easier to then extract the parent node from the path, I added a function to do that. It reversed the string to make it easy to start at the end and find the nodes. It discarded the first node (the current node), then found the next node, reversed the string again, and returned it as an int.

You should be able to copy/paste the whole code and try it. I hope it helps someone or gives you some ideas.

2020-01-02