表A:
id psort
10 0
11 0
13 0
我想把记录10,13,分别更新为2,5
即
id psort
10 2
11 0
13 5
请大家帮帮忙，用存储过程怎样实现？而不用在程序代码里循环几次实现更新
输入参数为
@IDS varchar,@PSORTS varchar
@IDS=10,13
@PSORTS =2,5
------解决方案--------------------
--try 看看
GO
create table A(id int,psort int)
insert into A select 10,0
insert into A select 11,0
insert into A select 13,0
Go
Create proc Ts
@ids varchar(30),
@psorts varchar(30)
AS
Create table #t1 (id int identity(1,1),c1 int)
Create table #t2 (id int identity(1,1),c2 int)
declare @sql varchar(1000)
select @sql='select '+replace(@ids,',',' union all select ')
insert into #t1(c1) exec(@sql)
select @sql='select '+replace(@psorts,',',' union all select ')
insert into #t2(c2) exec(@sql)
Update A
set psort=c2
from (select c1,c2 from #t1,#t2 where #t1.id=#t2.id) T
where A.id=T.c1
Go
exec Ts '10,13','2,5'
select * from A
/*
id psort
----------- -----------
10 2
11 0
13 5
*/
drop table A
drop proc Ts
------解决方案--------------------
要是在前台把参数整理成长度相等的，就不用这么麻烦了。
------解决方案--------------------
@a='1,21,3,11,12,7,9'
我得出来了 比如 @b='11',[email protected] 是第4个元素.
很遗憾, 当我知道一个元素在一个字串中是第一几个元素时,不通过循环(或function),或动态语句(或sp_executesql)我却无法得到它的值, 比如 @a的第6个元素. 我不知道它是什么.
(如果可以,那么就可以不借助临时表和动态语句)
- SQL code
create table A(id int,psort int) insert into A select 10,0 insert into A select 11,0 insert into A select 13,0 declare @IDS varchar(20),@PSORTS varchar(20),@sql VARCHAR(1000)SELECT @IDS='55,44,10,13,15,90',@PSORTS='1,2,3,4,5,2'create table #t(id int identity(1,1),c2 int)select @sql= 'select '+replace(@psorts, ',', ' union all select ') insert into #t(c2) exec(@sql) update a set a.psort=c2 from a inner join #t t on len(@ids)-len(replace(@ids,',',''))+1 -len(stuff(@ids,1,charindex(','+rtrim(a.id)+',',',[email protected]+',')+1,'')) +len(replace(stuff(@ids,1,charindex(','+rtrim(a.id)+',',',[email protected]+',')+1,''),',','')) =t.id where charindex(','+rtrim(a.id)+',',',[email protected]+',')>0select * from adrop table A,#t
------解决方案--------------------
- SQL code
create procedure updateTableA @idS varchar(100), @psortS varchar(100) ASBEGIN Declare @spot1 int, @spot2 int, @id varchar(100), @psort varchar(100), @sql varchar(200) WHILE @idS <> '' AND @psortS <> '' Begin SET @spot1 = Charindex(',', @idS) SET @spot2 = Charindex(',', @psortS) if @spot1 > 0 AND @spot2 > 0 Begin SET @id = cast(left(@idS, @spot1-1) as int) SET @psort = cast(left(@psortS, @spot2-1) as int) set @idS = right(@idS, len(@idS)[email protected]) set @psortS = right(@psortS, len(@psortS)[email protected]) END ELSE Begin SET @id = cast(@idS as int) set @psort = cast(@psortS as int) set @idS = '' set @psortS = '' END SET @sql = 'UPDATE A SET psort = ' + @psort + ' WHERE id= ' + @id exec(@sql) ENDEnddrop table Acreate table A(id int, psort int)insert into A select 10,0 insert into A select 11,0 insert into A select 13,0 GOdeclare @idS varchar(100),@PSORTS varchar(100)set @idS= '10,13'set @PSORTS = '2,5'exec updateTableA @idS, @Psorts select * from A