declare @a int,@b int,@c nvarcharset @b = 155
while(@b<>0)
begin
set @a=(select parentid from catalogs where id=@b)
if(@a <> '')
begin
while(@a<>0)
begin
set @c=','+cast(@a as nvarchar)+','+cast(@c as nvarchar)
set @a=(select parentid from catalogs where id=@a)
end
set @c=','+cast(@a as nvarchar)+','+cast(@c as nvarchar)
end
update catalogs set tree=@c where id=@b
set @b=@b-1end
while(@b<>0)
begin
set @a=(select parentid from catalogs where id=@b)
if(@a <> '')
begin
while(@a<>0)
begin
set @c=','+cast(@a as nvarchar)+','+cast(@c as nvarchar)
set @a=(select parentid from catalogs where id=@a)
end
set @c=','+cast(@a as nvarchar)+','+cast(@c as nvarchar)
end
update catalogs set tree=@c where id=@b
set @b=@b-1end
set @c = ''
while(@b>3)
begin
set @a=(select parentid from catalogs where id=@b)
if(@a <> '')
begin
--while(@a<>0)
begin
set @c=','+cast(@a as nvarchar)+','+cast(@c as nvarchar)
set @a=(select parentid from catalogs where id=@a)
end
set @c=','+cast(@a as nvarchar)+','+cast(@c as nvarchar)
end
update catalogs set tree=@c where id=@b
set @b=@b-1
end
----------
while(@a<>0)
你未对@a进行处理,死循环
@c nvarchar
应该给个长度,如@c nvarchar(100)
------------------------
use pubs
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'catalogs' AND type = 'U')
DROP TABLE catalogs
GOcreate TABLE catalogs (
id int identity(1,1) not null,
parentid int,
tree varchar(30)
)
GOinsert into catalogs(parentid)
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7
go
--------------------------------------
declare @a int,@b int,@c nvarchar(100)set @b = 5
set @c = ''while(@b>3)
begin
set @a=(select parentid from catalogs where id=@b)
if(@a <> '')
begin
-- while(@a<>0)
begin
set @c=','+cast(@a as nvarchar)+','+cast(@c as nvarchar)
set @a=(select parentid from catalogs where id=@a)
end
set @c=','+cast(@a as nvarchar)+','+cast(@c as nvarchar)
end
update catalogs set tree=@c where id=@b
set @b=@b-1
end
---------------------
DROP TABLE catalogs