create table t
(a int,b int)insert t
select 1,null union all
select 2,null union all
select 3,null
go
create function f_values(@value varchar(100),@id int)
returns int
as
begin
declare @i int
while @id>0
select @i=cast(left(@value,charindex(',',@value)-1) as int),
@value=stuff(@value,1,charindex(',',@value),''),
@id=@id-1
return @i
end
go
alter table t add id int identity(1,1)
go
declare @m varchar(20)
set @m='4,5,6,7,8,9'update t set b=dbo.f_values(@m,id)
go
alter table t drop column id
goselect * from t
drop function f_values
drop table ta b
----------- -----------
1 4
2 5
3 6(所影响的行数为 3 行)
(a int,b int)insert t
select 1,null union all
select 2,null union all
select 3,null
go
create function f_values(@value varchar(100),@id int)
returns int
as
begin
declare @i int
while @id>0
select @i=cast(left(@value,charindex(',',@value)-1) as int),
@value=stuff(@value,1,charindex(',',@value),''),
@id=@id-1
return @i
end
go
alter table t add id int identity(1,1)
go
declare @m varchar(20)
set @m='4,5,6,7,8,9'update t set b=dbo.f_values(@m,id)
go
alter table t drop column id
goselect * from t
drop function f_values
drop table ta b
----------- -----------
1 4
2 5
3 6(所影响的行数为 3 行)
set @i=4
update tablename set @b=@i,@i=@i+1
set @i=4
update tablename set b=@i,@i=@i+1
我的解决方案如下:
Create procedure test @values VARCHAR(8000)
AS
Begin
@i int
@id int
@thevalue VARCHAR(50)
select @i=count(*) from table1
While @i>0
Begin
select top 1 @id=id from table1 where id>=@i order by id
select @thevalue= 从@values 中分离出来当前的值.
update table1 set col=@thevalue where id=@id
select @i=@i+1
End
end exec test "6|5|4"
很久不用sql server,供楼主参考