create procedure EmployeeHierarchy 
@EmpID int
as
declare @out table (EmpID int, EmpName varchar(255), ReportsTo int)

insert into @out
select	EmpID, EmpName, ReportsTo
from	Employees_2000_2005
where	EmpID = @EmpID

while @@rowcount > 0
	insert into @out
	select	EmpID, EmpName, ReportsTo
	from	Employees_2000_2005
	where	ReportsTo in (select EmpID from @out)
		and EmpID not in (select EmpID from @out)

select o.EmpID, o.EmpName, e.EmpName Manager 
from 		@out o
inner join	Employees_2000_2005 e
	on	o.ReportsTo = e.EmpID
order by 1
go

USE [Test]
GO

/****** Object:  Table [dbo].[hier_test1]    Script Date: 08/27/2009 11:14:30 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hier_test1]') AND type in (N'U'))
DROP TABLE [dbo].[hier_test1]
GO

USE [Test]
GO

/****** Object:  Table [dbo].[hier_test1]    Script Date: 08/27/2009 11:14:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[hier_test1](
	[record_id] [int] IDENTITY(1,1) NOT NULL,
	[data1] [nvarchar](50) NULL,
	[data2] [nvarchar](2000) NULL,
	[hier_id] [hierarchyid] NOT NULL,
	[position] [nvarchar](50) NULL,
 CONSTRAINT [PK_hier_test1] PRIMARY KEY CLUSTERED 
(
	[record_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


insert into Test.dbo.hier_test1(
	data1,
	data2,
	hier_id
)VALUES(
	'/',
	'This is the root record',
	hierarchyid::GetRoot()
)


exec AddHierRecord 1, '/1', 'Child of Root'
exec AddHierRecord 1, '/2', 'Child of Root'
exec AddHierRecord 1, '/3', 'Child of Root'
exec AddHierRecord 2, '/1/1', 'Child of Child 1'
exec AddHierRecord 2, '/1/2', 'Child of Child 1'
exec AddHierRecord 2, '/1/3', 'Child of Child 1'
exec AddHierRecord 3, '/2/1', 'Child of Child 2'
exec AddHierRecord 3, '/2/2', 'Child of Child 2'
exec AddHierRecord 3, '/2/3', 'Child of Child 2'
exec AddHierRecord 4, '/3/1', 'Child of Child 3'
exec AddHierRecord 4, '/3/2', 'Child of Child 3'
exec AddHierRecord 4, '/3/3', 'Child of Child 3'
exec AddHierRecord 5, '/1/1/1', 'Child of Child 1 of Child 1'
exec AddHierRecord 5, '/1/1/2', 'Child of Child 1 of Child 1'
exec AddHierRecord 5, '/1/1/3', 'Child of Child 1 of Child 1'
exec AddHierRecord 6, '/1/2/1', 'Child of Child 2 of Child 1'
exec AddHierRecord 6, '/1/2/2', 'Child of Child 2 of Child 1'
exec AddHierRecord 6, '/1/2/3', 'Child of Child 2 of Child 1'


select *, hier_id.ToString()
from	hier_test1

declare @oldparent hierarchyid
declare @newparent hierarchyid
select @oldparent = hier_id
from	hier_test1
where	record_id = 2

select @newparent = hier_id
from	hier_test1
where	record_id = 3


update hier_test1
set		hier_id = hier_id.GetReparentedValue(@oldparent, @newparent)
where	hier_id.IsDescendantOf(@oldparent) = 1
	and record_id <> 2

select *, hier_id.ToString()
from	hier_test1

CREATE TABLE [dbo].[tblHierarchy](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [ParentId] [int] NULL,
  [Node] [hierarchyid] NOT NULL,
  [ParentNode]  AS ([Node].[GetAncestor]((1))) PERSISTED,
  [NodeLevel]  AS ([Node].[GetLevel]()) PERSISTED,
  [Code] [nvarchar](64) NOT NULL,
  [TypeId] [int] NOT NULL,
  [TargetId] [int] NULL,
  [CreateDate] [datetime] NOT NULL,
  [CreateUser] [nvarchar](32) NOT NULL,
  [UpdateDate] [datetime] NOT NULL,
  [UpdateUser] [nvarchar](32) NOT NULL,
  [UpdateCode] [nchar](1) NOT NULL,
  CONSTRAINT [tblHierarchy_PK] PRIMARY KEY NONCLUSTERED 
  (
    [Id] ASC
  ) ON [PRIMARY],
  CONSTRAINT [tblHierarchy_AK1] UNIQUE CLUSTERED 
  (
    [Node] ASC
  ) ON [PRIMARY]
) ON [PRIMARY]

SELECT
  [H1Id] = h1.Id
  ,[H1Code] = H1.Code
  ,[H1Type] = H1.TypeId
  ,[H2Grp] = H2.Id
  ,[H2Code] = H2.Code
  ,[H2Type] = H2.TypeId
  ,[H3Grp] = H3.Id
  ,[H3Code] = H3.Code
  ,[H3Type] = H3.TypeId
  ,[H4Grp] = H4.Id
  ,[H4Code] = H4.Code
  ,[H4Type] = H4.TypeId
FROM dbo.tblHierarchy H0 WITH (NOLOCK)
  INNER JOIN dbo.tblCATHierarchy H1 WITH (NOLOCK)
    ON H0.Id = H1.ParentId
  INNER JOIN dbo.tblCATHierarchy H2 WITH (NOLOCK)
    ON H1.Id = H2.ParentId
  INNER JOIN dbo.tblCATHierarchy H2 WITH (NOLOCK)
    ON H2.Id = H3.ParentId
  INNER JOIN dbo.tblCATHierarchy H4 WITH (NOLOCK)
    ON H3.Id = H4.ParentId
WHERE H0.Code = 'ROOT'
  AND H1.TypeId = 1

SELECT
  [H1Id] = h1.Id
  ,[H1Code] = H1.Code
  ,[H1Type] = H1.TypeId
  ,[H2Grp] = H2.Id
  ,[H2Code] = H2.Code
  ,[H2Type] = H2.TypeId
  ,[H3Grp] = H3.Id
  ,[H3Code] = H3.Code
  ,[H3Type] = H3.TypeId
  ,[H4Grp] = H4.Id
  ,[H4Code] = H4.Code
  ,[H4Type] = H4.TypeId
FROM dbo.tblHierarchy H0 WITH (NOLOCK)
  INNER JOIN dbo.tblCATHierarchy H1 WITH (NOLOCK)
    ON H0.Node = H1.Node.GetAncestor(1)
  INNER JOIN dbo.tblCATHierarchy H2 WITH (NOLOCK)
    ON H1.Node = H2.Node.GetAncestor(1)
  INNER JOIN dbo.tblCATHierarchy H2 WITH (NOLOCK)
    ON H2.Node = H3.Node.GetAncestor(1)
  INNER JOIN dbo.tblCATHierarchy H4 WITH (NOLOCK)
    ON H3.Node = H4.Node.GetAncestor(1)
WHERE H0.Code = 'ROOT'
  AND H1.TypeId = 1