.png)
IsDescendantOf (Database Engine)
SQL Server 2008 R2
Returns true if this is a descendant ofparent .
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)
