IsDescendantOf (Database Engine)

Article
11/18/2022

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Returns true if this is a descendant of parent.

Syntax

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

-- CLR syntax  
SqlHierarchyId IsDescendantOf (SqlHierarchyId parent )

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

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)