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'
