求救，根据多ID更新多字段
需要传3个参数，2个ID(双主键)，1个时间。都是数组形式的
ID1 = '1,2,3,4 '
ID2 = '101,102,103,104 '
TDay = '2007-1-3,2007-1-4,2007-1-15,2007-2-6 '
每组都是相同数量的个数，比如都是4组数
例如：update [Table] set TDay = '2007-1-3 ' where ID1 =1 AND ID2 = 101
更新4组，何如在存储过程中实现啊，谢谢大家了！
------解决方案--------------------
create table T(ID1 int, ID2 int, TDay datetime)
insert T select 1, 101, null
insert T select 2, 102, null
insert T select 3, 103, null
insert T select 4, 104, null
insert T select 5, 105, null
create proc pc(@ID1 varchar(200), @ID2 varchar(200), @TDay varchar(1000))
as
declare @tb table(ID1 int, ID2 int, TDay datetime)
while(charindex( ', ', @ID1)> 0)
begin
insert @tb select
substring(@ID1, 1, charindex( ', ', @ID1)-1),
substring(@ID2, 1, charindex( ', ', @ID2)-1),
substring(@TDay, 1, charindex( ', ', @TDay)-1)
select @ID1=stuff(@ID1, 1, charindex( ', ', @ID1), ' '),
@ID2=stuff(@ID2, 1, charindex( ', ', @ID2), ' '),
@TDay=stuff(@TDay, 1, charindex( ', ', @TDay), ' ')
end
insert @tb select @ID1, @ID2, @TDay
update T set TDay=B.TDay
from @tb as B
where T.ID1=B.ID1 and T.ID2=B.ID2
go
declare @ID1 varchar(200), @ID2 varchar(200), @TDay varchar(1000)
select
@ID1 = '1,2,3,4 ',
@ID2 = '101,102,103,104 ',
@TDay = '2007-1-3,2007-1-4,2007-1-15,2007-2-6 '
exec pc @ID1, @ID2, @TDay
go
select * from T
--result
ID1 ID2 TDay
----------- ----------- ------------------------------------------------------
1 101 2007-01-03 00:00:00.000
2 102 2007-01-04 00:00:00.000
3 103 2007-01-15 00:00:00.000
4 104 2007-02-06 00:00:00.000
5 105 NULL
------解决方案--------------------
CREATE FUNCTION dbo.f_splitstr(
@str varchar(8000)
)RETURNS @r TABLE(id int IDENTITY(1, 1), value varchar(5000))
AS
BEGIN
DECLARE @pos int
SET @pos = CHARINDEX( ', ', @str)
WHILE @pos > 0
BEGIN
INSERT @r(value) VALUES(LEFT(@str, @pos - 1))
SELECT
@str = STUFF(@str, 1, @pos, ' '),
@pos = CHARINDEX( ', ', @str)
END
IF @str > ' '
INSERT @r(value) VALUES(@str)
RETURN
END
GO
-- 调用函数实现处理
DECLARE @id1s varchar(8000), @id2s varchar(8000), @dates varchar(8000)
SELECT
@id1s = '1,2,3,4 ',
@id2s = '101,102,103,104 ',
@dates= '2007-1-3,2007-1-4,2007-1-15,2007-2-6 '
--UPDATE A SET TDay = B.dt
--FROM [Table] A,(
SELECT
id1 = CONVERT(int, ID1.value),
id2 = CONVERT(int, ID2.value),
dt = CONVERT(datetime, DT.value)
FROM dbo.f_splitstr(@id1s) ID1, dbo.f_splitstr(@id2s) ID2, dbo.f_splitstr(@dates) DT
WHERE ID1.id = ID2.id
AND ID1.id = DT.id
--)B
--WHERE A.ID1 = B.ID1 AND A.ID2 = B.ID2
GO
DROP FUNCTION f_splitstr
------解决方案--------------------
--创建一个分割字符串的函数
CREATE FUNCTION dbo.f_splitSTR(