-- The script updates the FullPath column of the table according to the values 
-- in Title column and hirNodeParent Column


DECLARE
    @hirNode int,
    @hirNodeParent int,
    @Title varchar(500),
    @Return varchar(500),
    @LasthirNode int,
    @Node int,
    @FullPath varchar(1000)

Select hirNode into #hirNodes from hirNodes 

While exists (Select * From #hirNodes)
Begin
	
	Set @hirNode = (Select Top 1 hirnode from #hirNodes)
	Set @Node = @hirNode
	Set @FullPath = (select FullPath from hirNodes where hirNode = @hirNode)

	SET @Return = ''
	WHILE @hirNode > 0
	BEGIN
		SELECT @hirNodeParent = hirNodeParent, @Title = Title
		FROM hirNodes
		WHERE hirNode = @hirNode
		SET @LasthirNode = @hirNode
		SET @Return = @Title + '\' + @Return
		SET @hirNode = @hirNodeParent
	END
	
	SET @Return = '\\' + @Return
	
	if(@FullPath <> @Return)
	begin
		Update hirNodes set FullPath = @Return where hirNode = @Node
	end
	
	Delete from #hirNodes where hirNode = @Node
	
	print(@Node)
End

Drop table #hirNodes


--Table Script

CREATE TABLE [dbo].[hirNodes](
	[hirNode] [int] IDENTITY(1,1) NOT NULL,
	[hirNodeParent] [int] NULL,
	[hirLevel] [int] NOT NULL,
	[Title] [varchar](500) NULL,
	[Brief] [varchar](16) NULL,
	[Description] [varchar](256) NULL,
	[Active] [bit] NOT NULL,
	[DisplayOrder] [varchar](50) NULL,
	[FullPath] [varchar](500) NULL,
 CONSTRAINT [PK_hirNodes_1] PRIMARY KEY CLUSTERED 
(
	[hirNode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

/****** Object:  Index [idx_hirNodes_fullPath]    Script Date: 10/31/2012 19:39:16 ******/
CREATE NONCLUSTERED INDEX [idx_hirNodes_fullPath] ON [dbo].[hirNodes] 
(
	[FullPath] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object:  Index [idx_hirNodes_hirLevel]    Script Date: 10/31/2012 19:39:52 ******/
CREATE NONCLUSTERED INDEX [idx_hirNodes_hirLevel] ON [dbo].[hirNodes] 
(
	[hirLevel] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object:  Index [idx_hirNodes_hirNodeParent]    Script Date: 10/31/2012 19:41:47 ******/
CREATE NONCLUSTERED INDEX [idx_hirNodes_hirNodeParent] ON [dbo].[hirNodes] 
(
	[hirNodeParent] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object:  Index [idx_hirNodes_Title]    Script Date: 10/31/2012 19:47:55 ******/
CREATE NONCLUSTERED INDEX [idx_hirNodes_Title] ON [dbo].[hirNodes] 
(
	[Title] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

drop table #hirNode
go
Create Table #hirNode(
	hirNode Int
	,hirNodeParent int
	,Title varchar(100)
)
Insert into #hirNode
values (1,NULL,'Start')
,(2,1,'Second')
,(3,2,'Third')
,(4,NULL,'Start2')
,(5,4,'Second2');


WITH BuildPath(hirNode,hirNodeParent, NodeLevel, NodePath)
AS (SELECT    hirNode,
        hirNodeParent,
        1,
        CONVERT(varchar(255), '\\'+Title)
    FROM #hirNode 
    WHERE hirNodeParent IS NULL  --Start at the Top
    UNION ALL
    SELECT 
        h.hirNode,
        h.hirNodeParent,
        NodeLevel + 1,
        CONVERT (varchar(255), RTRIM(NodePath) + '\' + Title)
    FROM #hirNode AS h
    JOIN BuildPath AS p ON h.hirNodeParent = p.hirNode
    )
Select * from BuildPath

drop table #hirNode
go
Create Table #hirNode(
	hirNode Int
	,hirNodeParent int
	,Title varchar(100)
)
Insert into #hirNode
values (1,NULL,'Start')
,(2,1,'Second')
,(3,2,'Third')
,(4,NULL,'Start2')
,(5,4,'Second2');


WITH BuildPath(hirNode,hirNodeParent, NodeLevel, NodePath)
AS (SELECT    hirNode,
        hirNodeParent,
        1,
        CONVERT(varchar(255), '\\'+Title)
    FROM #hirNode 
    WHERE hirNodeParent IS NULL  --Start at the Top
    UNION ALL
    SELECT 
        h.hirNode,
        h.hirNodeParent,
        NodeLevel + 1,
        CONVERT (varchar(255), RTRIM(NodePath) + '\' + Title)
    FROM #hirNode AS h
    JOIN BuildPath AS p ON h.hirNodeParent = p.hirNode
    )
Update
	hr
Set
	FullPath=bp.NodePath
From 
	hirNodes hr
	JOIN BuildPath bp on hr.hirNode=bp.hirNode