GetAncestor (Database Engine)

Article
09/03/2024

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

Returns a hierarchyid representing the nth ancestor of this.

Syntax

-- Transact-SQL syntax  
child.GetAncestor ( n )

-- CLR syntax  
SqlHierarchyId GetAncestor ( int n )

Arguments

n
An int, representing the number of levels to go up in the hierarchy.

Return types

SQL Server return type:hierarchyid

CLR return type:SqlHierarchyId

Remarks

Used to test whether each node in the output has the current node as an ancestor at the specified level.

If a number greater than GetLevel() is passed, NULL is returned.

If a negative number is passed, an exception is raised.

Examples

A. Finding the child nodes of a parent

GetAncestor(1) returns the employees that have david0 as their immediate ancestor (their parent). The following example uses GetAncestor(1).

DECLARE @CurrentEmployee hierarchyid  
SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeDemo  
WHERE LoginID = 'adventure-works\david0'  
  
SELECT OrgNode.ToString() AS Text_OrgNode, *  
FROM HumanResources.EmployeeDemo  
WHERE OrgNode.GetAncestor(1) = @CurrentEmployee ;

B. Returning the grandchildren of a parent

GetAncestor(2) returns the employees that are two levels down in the hierarchy from the current node. These employees are the grandchildren of the current node. The following example uses GetAncestor(2).

DECLARE @CurrentEmployee hierarchyid  
SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeDemo  
WHERE LoginID = 'adventure-works\ken0'  
  
SELECT OrgNode.ToString() AS Text_OrgNode, *  
FROM HumanResources.EmployeeDemo  
WHERE OrgNode.GetAncestor(2) = @CurrentEmployee ;

C. Returning the current row

To return the current node by using GetAncestor(0), execute the following code.

DECLARE @CurrentEmployee hierarchyid  
SELECT @CurrentEmployee = OrgNode FROM HumanResources.EmployeeDemo  
WHERE LoginID = 'adventure-works\david0'  
  
SELECT OrgNode.ToString() AS Text_OrgNode, *  
FROM HumanResources.EmployeeDemo  
WHERE OrgNode.GetAncestor(0) = @CurrentEmployee ;

D. Returning a hierarchy level if a table isn't present

GetAncestor returns the selected level in the hierarchy even if a table isn't present. For example, the following code specifies a current employee and returns the hierarchyid of the ancestor of the current employee without reference to a table.

DECLARE @CurrentEmployee hierarchyid ;  
DECLARE @TargetEmployee hierarchyid ;  
SELECT @CurrentEmployee = '/2/3/1.2/5/3/' ;  
SELECT @TargetEmployee = @CurrentEmployee.GetAncestor(2) ;  
SELECT @TargetEmployee.ToString(), @TargetEmployee ;

E. Calling a common language runtime method

The following code snippet calls the GetAncestor() method.

this.GetAncestor(1)

Share via