• Article

IsDescendantOf (Database Engine)

Returns true if this is a descendant ofparent.

Syntax

-- Transact-SQL syntax
child.IsDescendantOf (parent)

-- CLR syntax
SqlHierarchyId IsDescendantOf (SqlHierarchyIdparent)

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)