SQL 2008 HierarchyID - Select X descendants down

Posted by NTulip on Stack Overflow See other posts from Stack Overflow or by NTulip
Published on 2010-05-04T20:14:23Z Indexed on 2010/05/04 20:18 UTC
Read the original article Hit count: 229

How can I query a table which has a column of data type HIERARCHYID and get a list of descendants X levels deep under an employee?

Here is the current structure:

CREATE TABLE [dbo].[Employees](
    [NodeId] [hierarchyid] NOT NULL,
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](120) NULL,
    [MiddleInitial] [varchar](1) NULL,
    [LastName] [varchar](120) NULL,
    [DepartmentId] [int] NULL,
    [Title] [varchar](120) NULL,
    [PhoneNumber] [varchar](20) NULL,
    [IM] [varchar](120) NULL,
    [Photo] [varbinary](max) NULL,
    [Bio] [varchar](400) NULL,
    [Active] [bit] NULL,
    [ManagerId] [int] NULL
)

© Stack Overflow or respective owner

Related posts about sql-server-2008

Related posts about hierarchical-data