How to find ALL descendants using HierarchyID for SQL Server

Question

I need to find all descendants of a category using HierarchyID for SQL Server. I know how to find direct childs but I would like to find childs of childs of child..etc. Is there a way to do this using the hierarchyID.

Answer 1

If you have the root of the tree you wan't, can't you just use:

declare @root hierarchyID;

select @root = col
from yourTable
where [whatever uniquely identifies this row]

select *
from yourTable
where col.IsDescendantOf(@rootObj) = 1

Answer 2

I'll assume for my example that your table is something like the following:

DECLARE TABLE MyTable
(
    HID hierarchyid PRIMARY KEY,
    ID int IDENTITY(1, 1),
    SomeText varchar(50)
);

If you want all decendants of a node with ID 3, down to a max level (from root) of 5:

DECLARE @searchNode hierarchyid;

SELECT @searchNode = HID
FROM MyTable
WHERE ID = 3;

SELECT *
FROM MyTable
WHERE HID.IsDescendantOf(@searchNode)
AND HID.GetLevel() <= 5;

If instead you want 2 levels of children under the requested node you would need to capture the level of your search node in the first select and change the comparison to something like

WHERE HID.IsDescendantOf(@searchNode) = 1 
AND HID.GetLevel() <= (@searchLevel + 2);