Resolve the branch for all nodes in the node tree

SQL to resolve the path from the root to the particular node in both node name and node id form.
(Demonstrates cursors, converting int to string, concatenating strings and selecting into variables)

drop table NodePaths

create table NodePaths
(Id int,
NamePath varchar(1000),
IdPath char(500)
)

declare @thisname as varchar(200)
declare @fullpath as varchar(1000)
declare @idpath as varchar(500)
declare @startid as int, @pnid as int, @currentid as int
declare @thisid as varchar(10)

declare c_1 cursor for
select id from node

open c_1
fetch c_1 into @currentid

while @@fetch_status = 0
begin

set @startid = @currentid

select @pnid = parentnodeid, @fullpath=[name], @idpath = id from node where id = @startid

while @pnid <> -1
begin
select @thisname = [name], @thisid = CONVERT(varchar(10),id), @pnid = parentnodeid from node where id = @pnid
set @fullpath = @thisname + ' > ' + @fullpath
set @idpath = @thisid + ' > ' + @idpath
end

insert into NodePaths
select @startid,@fullpath,@idpath

fetch c_1 into @currentid
end

close c_1
deallocate c_1