这个存储过程怎么写？

[purexiafeng]

有张表
IDS IDTO IDCOST
1 1 20
1 2 0
1 3 10
1 5 10
都是int型的

问题是：
写一个存储过程有3个参数 （IDS int ，strIDTO varchar（255），strIDCOST varchar（255））
strIDTO 里面是用逗号隔开的字符串，比如strIDTO="1,2,3,5"
strIDCOST ="30,10,20,10"
根据传进来的参数，修改表中IDCOST的值，strIDTO和strIDCOST 中的值是一一对应的

请帮下忙!

[paoluo]

--創建測試環境
Create Table 表
(IDS Int,
IDSTO Int,
IDSCOST Int)
--插入數據
Insert 表 Select 1, 1, 20
Union All Select 1, 2, 0
Union All Select 1, 3, 10
Union All Select 1, 5, 10
GO
--創建存儲過程
Create ProceDure SP_Update(@IDS Int, @IDSTO Varchar(255), @IDSCOST Varchar(255))
As
Begin
While CharIndex(',', @IDSTO) > 0 And CharIndex(',', @IDSCOST) > 0
Begin
Update 表 Set IDSCOST = Substring(@IDSCOST, 1, CharIndex(',', @IDSCOST) - 1)
Where IDSTO = Substring(@IDSTO, 1, CharIndex(',', @IDSTO) - 1) And IDS = @IDS
Select @IDSTO = Stuff(@IDSTO, 1, CharIndex(',', @IDSTO), ''), @IDSCOST = Stuff(@IDSCOST, 1, CharIndex(',', @IDSCOST), '')
End
End
GO
--測試
EXEC SP_Update 1, '1,2,3,5', '30,10,20,10'

Select * From 表

GO
--刪除測試環境
Drop Table 表
Drop ProceDure SP_Update
--結果
/*
IDS IDSTO IDSCOST
1 1 30
1 2 10
1 3 20
1 5 10
*/

[dawugui]

create table tb(IDS int,IDTO int,IDCOST int)
insert into tb values(1, 1, 20)
insert into tb values(1, 2, 0)
insert into tb values(1, 3, 10)
insert into tb values(1, 5, 10)
go

create procedure sp_update(@idto varchar(50), @idcost varchar(100))
As
begin
while charindex(',' , @idto) > 0
begin
update tb
set idcost = left(@idcost,charindex(',' , @idcost) - 1)
where idto = left(@idto,charindex(',' , @idto) - 1)
set @idto = substring(@idto,charindex(',' , @idto) + 1 , len(@idto))
set @idcost = substring(@idcost,charindex(',' , @idcost) + 1 , len(@idcost))
end
end
exec sp_update '1,2,3,5','30,10,20,10'
select * from tb
drop table tb
drop procedure sp_update

[dawugui]

create table tb(IDS int,IDTO int,IDCOST int)
insert into tb values(1, 1, 20)
insert into tb values(1, 2, 0)
insert into tb values(1, 3, 10)
insert into tb values(1, 5, 10)
go
create procedure sp_1(@idto varchar(50), @idcost varchar(100))
As
begin
while charindex(',' , @idto) > 0
begin
update tb
set idcost = left(@idcost,charindex(',' , @idcost) - 1)
where idto = left(@idto,charindex(',' , @idto) - 1)
set @idto = substring(@idto,charindex(',' , @idto) + 1 , len(@idto))
set @idcost = substring(@idcost,charindex(',' , @idcost) + 1 , len(@idcost))
end
update tb
set idcost = @idcost
where idto = @idto
end
go

exec sp_1 '1,2,3,5','30,10,20,11'
select * from tb
drop table tb
drop procedure sp_1
/*
IDS IDTO IDCOST
----------- ----------- -----------
1 1 30
1 2 10
1 3 20
1 5 11

（所影响的行数为 4 行）
*/