TechNet
Export (0) Print
Expand All

IsDescendantOf (Database Engine)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns true if this is a descendant of parent.

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

  
-- CLR syntax  
SqlHierarchyId IsDescendantOf (SqlHierarchyId parent )  

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.

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)  

hierarchyid Data Type Method Reference
Hierarchical Data (SQL Server)
hierarchyid (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft