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