字段‘ans’里面的数据应该是 ‘A,B,C,D’形式的,但是出错了变成了‘ABCD’这种形式的。
于是写了个游标想改掉,可总是报“消息 170,级别 15,状态 1,第 1 行第 1 行: 'begin' 附近有语法错误。”
不知道为什么。declare @tmid nvarchar(50);
declare @ans nvarchar(50);
declare curr cursor for select tmid,ans from z_tbtk where category=5 and ans not like'%,%' for update;
open curr;
fetch curr into @tmid,@ans;
while (@@fetch_status = 0)
begin
-----
declare @newAns nvarchar(50);
set @newAns = '';
declare @i int;
set @i = 1;
while (len(@ans) >= @i)
begin
set @newAns = @newAns + substring(@ans,@i,1) + ',';
set @ans = @ans + 1;
end
--print (@newAns);
update z_tbtk set ans = @ans where tmid = @tmid;
----- fetch next from curr into @tmid,@ans;
end
close curr;
deallocate curr;把红色部分去掉就不会报错了!
想问下这是为什么,再有这个问题有没有更简单的解决办法不要写这么就可以把错的格式改过来。
于是写了个游标想改掉,可总是报“消息 170,级别 15,状态 1,第 1 行第 1 行: 'begin' 附近有语法错误。”
不知道为什么。declare @tmid nvarchar(50);
declare @ans nvarchar(50);
declare curr cursor for select tmid,ans from z_tbtk where category=5 and ans not like'%,%' for update;
open curr;
fetch curr into @tmid,@ans;
while (@@fetch_status = 0)
begin
-----
declare @newAns nvarchar(50);
set @newAns = '';
declare @i int;
set @i = 1;
while (len(@ans) >= @i)
begin
set @newAns = @newAns + substring(@ans,@i,1) + ',';
set @ans = @ans + 1;
end
--print (@newAns);
update z_tbtk set ans = @ans where tmid = @tmid;
----- fetch next from curr into @tmid,@ans;
end
close curr;
deallocate curr;把红色部分去掉就不会报错了!
想问下这是为什么,再有这个问题有没有更简单的解决办法不要写这么就可以把错的格式改过来。
declare @newAns nvarchar(50);
set @newAns = '';
declare @i int;
set @i = 1;
while (len(@ans) >= @i)
begin
set @newAns = @newAns + substring(@ans,@i,1) + ',';
set @ans = @ans + 1;
end
--print (@newAns);
update z_tbtk set ans = @ans where tmid = @tmid;
insert into z_tbtk select 1,'ABCD',5
insert into z_tbtk select 2,'EFGH',5
declare @tmid nvarchar(50);
declare @ans nvarchar(50);
declare curr cursor for select tmid,ans from z_tbtk where category=5 and ans not like'%,%'-- for update;
open curr;
fetch curr into @tmid,@ans;
while (@@fetch_status = 0)
begin
declare @newAns nvarchar(50);
set @newAns = '';
declare @i int;
set @i = 1;
while (len(@ans) >= @i)
begin
set @newAns = @newAns + substring(@ans,@i,1) + ',';
set @i = @i + 1;
end
--print (@newAns);
update z_tbtk set ans = left(@newAns,len(@newAns)-1) where tmid = @tmid;
fetch next from curr into @tmid,@ans;
end
close curr;
deallocate curr;
go
select * from z_tbtk
drop table z_tbtk
/*
tmid ans category
----------- ---------- -----------
1 A,B,C,D 5
2 E,F,G,H 5(2 行受影响)
*/