If you are interested in traversing the hierarchy then you can do it with the sampel SP which builds the tree using @@rowcount variable. Hope this helps. If its irrelevant then accpet my applogies.
Regards,
Affan
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_BuildHierarchy
AS
BEGIN
-- Deletes all the data from Export Table
DELETE FROM ExpTblCostCentre
-- Inserts all the data from Cost Centre Table to Export Table
INSERT INTO ExpTblCostCentre (Id, EmployeeId, Name, ParentId, ParentEmployeeId)
SELECT Id, EmployeeId, Code, ParentId, ParentEmployeeId FROM CostCentre
-- Updates the Hierarchy IDs and strings for the first level in the export table
UPDATE A
SET A.CostCentreIDString = CAST(B.ID AS VARCHAR) + ':' + CAST(B.EmployeeID AS VARCHAR)
, A.CostCentreNameString = b.Code
FROM ExpTblCostCentre A
INNER JOIN CostCentre B
ON A.[ID] = B.[ID] AND A.EmployeeID = B.EmployeeID
AND A.ParentID IS NULL AND A.ParentEmployeeId IS NULL
WHILE @@ROWCOUNT > 0
BEGIN
-- Updates the Hierarchy IDs and strings
-- for All the levels in the export table
UPDATE Chld
SET Chld.CostCentreIDString = CAST(Prnt.CostCentreIDString AS VARCHAR(8000)) + ':' + CAST(Chld.[ID] AS VARCHAR(255)),
Chld.CostCentreNameString = Prnt.CostCentreNameString + ':' + Chld.[Name]
FROM ExpTblCostCentre Chld
INNER JOIN ExpTblCostCentre PRnt
ON Chld.ParentID = Prnt.[ID] AND Chld.ParentEmployeeID = Prnt.EmployeeID
WHERE Prnt.CostCentreNameString IS NOT NULL AND Chld.CostCentreNameString IS NULL
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO