有张表
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 中的值是一一对应的请帮下忙!
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 中的值是一一对应的请帮下忙!
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
*/
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)
gocreate 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
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
goexec 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 行)
*/