求一条更新语句－－根据id循环更新产品顺序,该怎么解决

求一条更新语句－－根据id循环更新产品顺序
表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',我得出它在@a中的位置 是第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)+',',','+@ids+',')+1,''))        +len(replace(stuff(@ids,1,charindex(','+rtrim(a.id)+',',','+@ids+',')+1,''),',',''))         =t.id    where charindex(','+rtrim(a.id)+',',','+@ids+',')>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)-@spot1)            set @psortS = right(@psortS, len(@psortS)-@spot2)        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