Returns true if this is a descendant ofparent.
-- Transact-SQL syntax child.IsDescendantOf ( parent )
-- CLR syntax SqlHierarchyId IsDescendantOf ( SqlHierarchyId parent )
The hierarchyid node for which the IsDescendantOf test should be performed.
SQL Server return type: bit
CLR return type: SqlBoolean
Returns true for all the nodes in the sub-tree rooted at parent, and false for all other nodes.
Parent is considered its own descendant.
The following example returns a manager and the employees that report to the manager:
DECLARE @Manager hierarchyid SELECT @Manager = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = 'adventure-works\dylan0' SELECT * FROM HumanResources.EmployeeDemo WHERE @OrgNode.IsDescendantOf(Manager) = 1
The following code declares and populates three variables. It then evaluates the hierarchical relationship and returns one of two printed results based on the comparison:
DECLARE @Manager hierarchyid, @Employee hierarchyid, @LoginID nvarchar(256) SELECT @Manager = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = 'adventure-works\terri0' ; SELECT @Employee = OrgNode, @LoginID = LoginID FROM HumanResources.EmployeeDemo WHERE LoginID = 'adventure-works\rob0' IF @Employee.IsDescendantOf(@Manager) = 1 BEGIN PRINT 'LoginID ' + @LoginID + ' is a subordinate of the selected Manager.' END ELSE BEGIN PRINT 'LoginID ' + @LoginID + ' is not a subordinate of the selected Manager.' ; END
The following code snippet calls the IsDescendantOf() method.
IsDescendantOf()
this.IsDescendantOf(Parent)
WHERE OrgNode.IsDescendantOf(@Manager) = 1