Export (0) Print
Expand All

GetAncestor (Database Engine)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a hierarchyid representing the nth ancestor of this.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.


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

-- CLR syntax
SqlHierarchyId GetAncestor ( int n )

n

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

SQL Server return type: hierarchyid

CLR return type: SqlHierarchyId

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.

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 ;

GetAncestor(2) returns the employees that are two levels down in the hierarchy from the current node. These 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 ;

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 ;

GetAncestor returns the selected level in the hierarchy even if a table is not present. For example the following code designates 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 ;

The following code snippet calls the GetAncestor() method.

this.GetAncestor(1)
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft