Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Returns true if this is a descendant of parent.
Syntax
-- Transact-SQL syntax
child. IsDescendantOf ( parent )
-- CLR syntax
SqlHierarchyId IsDescendantOf (SqlHierarchyId parent )
Arguments
- parent
The hierarchyid node for which the IsDescendantOf test should be performed.
Return Types
SQL Server return type: bit
CLR return type: SqlBoolean
Remarks
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.
Examples
A. Using IsDescendantOf in a WHERE clause
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
B. Using IsDescendantOf to evaluate a relationship
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
C. Calling a common language runtime method
The following code snippet calls the IsDescendantOf() method.
this.IsDescendantOf(Parent)