(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
No comments:
Post a Comment