Thursday, June 2, 2011

SQL Server 2008 - Hierarchy ID Data Type

create table [dbo].Organisation
(
DepartmentID int primary key nonclustered,
DepartmentName varchar(100) not null,
DepartmentHierarchyNode hierarchyid not null,
DepartmentHierarchyLevel as DepartmentHierarchyNode.GetLevel() persisted
)

insert Organisation(DepartmentID, DepartmentHierarchyNode, DepartmentName)
values (1, hierarchyid::GetRoot(), 'Contoso')

select * from Organisation

create procedure AddDepartment
@DepartmentID int,
@DepartmentName varchar(100),
@ParentID int
as
begin
declare @ParentNode hierarchyid,
@MaxChildNode hierarchyid
begin transaction
select @ParentNode = DepartmentHierarchyNode
from Organisation
where DepartmentID = @ParentID

select @MaxChildNode = max(DepartmentHierarchyNode)
from Organisation
where @ParentNode = DepartmentHierarchyNode.GetAncestor(1)

insert Organisation (DepartmentID, DepartmentHierarchyNode, DepartmentName)
values (@DepartmentID, @ParentNode.GetDescendant(@MaxChildNode, null), @DepartmentName)
commit
end


exec AddDepartment 2, 'Operations', 1
exec AddDepartment 3, 'Development', 1
exec AddDepartment 4, 'Parking', 1
exec AddDepartment 5, 'Home Operations', 2
exec AddDepartment 6, 'Field Operations', 2
exec AddDepartment 7, 'North Territory', 6
exec AddDepartment 8, 'South Territory', 6
exec AddDepartment 9, 'Database', 3
exec AddDepartment 10, 'Services', 3
exec AddDepartment 11, 'Applications', 3
exec AddDepartment 12, 'Windows', 11
exec AddDepartment 13, 'Internet', 11
exec AddDepartment 14, 'Self', 4
exec AddDepartment 15, 'Valet', 4

select * from Organisation

create procedure ShowDepartmentChart
@DepartmentName varchar(50)
as
begin
declare @TopNode hierarchyid,
@TopLevel int

select @TopNode = DepartmentHierarchyNode,
@TopLevel = DepartmentHierarchyLevel
from Organisation
where DepartmentName = @DepartmentName

select DepartmentHierarchyNode.ToString() NodeText, space((DepartmentHierarchyLevel - @TopLevel) * 5) + DepartmentName Department
from Organisation
where DepartmentHierarchyNode.IsDescendantOf(@TopNode) = 1
order by DepartmentHierarchyNode
end
go

exec ShowDepartmentChart 'Contoso'