--生成测试数据 create table a(name varchar(10),result int) insert into a select 'a',1 insert into a select 'a',2 insert into a select 'a',3 insert into a select 'a',4 go--创建存储过程 create procedure sp_update( @name varchar(10), @result varchar(8000)) as begin delete a where name=@name set @result=replace(@result,' ','') while charindex(',',@result)>0 begin if isnumeric(left(@result,charindex(',',@result)-1))=0 return
insert into a select @name,cast(left(@result,charindex(',',@result)-1) as int)
set @result=stuff(@result,1,charindex(',',@result),'') end if len(@result)>0 insert into a select @name,@result end go--查看表中数据 select * from a /* name result ---------- ----------- a 1 a 2 a 3 a 4 */--执行存储过程 exec sp_update 'a','3,8'--查看执行结果 select * from a /* name result ---------- ----------- a 3 a 8 */--删除测试环境 drop procedure sp_update drop table a go
create table a(name varchar(10),result int)
insert into a select 'a',1
insert into a select 'a',2
insert into a select 'a',3
insert into a select 'a',4
go--创建存储过程
create procedure sp_update(
@name varchar(10),
@result varchar(8000))
as
begin
delete a where name=@name
set @result=replace(@result,' ','')
while charindex(',',@result)>0
begin
if isnumeric(left(@result,charindex(',',@result)-1))=0
return
insert into a select @name,cast(left(@result,charindex(',',@result)-1) as int)
set @result=stuff(@result,1,charindex(',',@result),'')
end
if len(@result)>0
insert into a select @name,@result
end
go--查看表中数据
select * from a
/*
name result
---------- -----------
a 1
a 2
a 3
a 4
*/--执行存储过程
exec sp_update 'a','3,8'--查看执行结果
select * from a
/*
name result
---------- -----------
a 3
a 8
*/--删除测试环境
drop procedure sp_update
drop table a
go