IsDescendantOf (Database Engine)
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)
See also
hierarchyid Data Type Method Reference
Hierarchical Data (SQL Server)
hierarchyid (Transact-SQL)
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for