SQL Server - Recursively Find all Parents and Children in a Table with Parent Child Relationships

The first of the two SQL scripts, below, will return a list of all Parent rows for any table with a Parent/Child relationship.  The second will return a list of Child rows.



--Get Parent Row IDs (example: Table=WebMenu, ID=MenuID, ParentID=ParentMenuID)

DECLARE @MenuItemID INT;
SET @MenuItemID = 1001;

WITH MenuParentList AS (

    SELECT MenuID, ParentMenuID
    FROM WebMenu
    WHERE MenuID = @MenuItemID

    UNION ALL
   
    SELECT e1.MenuID, e1.ParentMenuID
    FROM WebMenu e1
    INNER JOIN MenuParentList e2 ON e2.ParentMenuID = e1.MenuID
)

SELECT *
FROM MenuParentList OPTION (MAXRECURSION 25)
GO

--Get Child Row IDs (example: Table=WebMenu, ID=MenuID, ParentID=ParentMenuID)

DECLARE @MenuItemID INT;
SET @MenuItemID = 1001;

WITH MenuChildList AS (

    SELECT MenuID, ParentMenuID
    FROM WebMenu
    WHERE MenuID = @MenuItemID

    UNION ALL
  
    SELECT e1.MenuID, e1.ParentMenuID
    FROM WebMenu e1
    INNER JOIN MenuChildList e2 ON e2.MenuID = e1.ParentMenuID
)

SELECT *
FROM MenuChildList  OPTION (MAXRECURSION 25)
GO