传3个参数,都是数组形式还有时间类型用存储过程更新
@Oid = 1,2,3,4
@Did = 111,222,333,444
@DateArr = '2007-1-1,2007-1-2,2007-1-3,2007-1-4'
CREATE proc Test999 @Oid nvarchar(1000) --ID1 ,@Did nvarchar(1000) --ID2 ,@DateArr nvarchar(1000) --日期 AS DECLARE @id1s varchar(8000), @id2s varchar(8000), @dates varchar(8000) set @id1s=@Oid set @id2s=@Did set @dates = @DateArr -- 调用函数实现处理 SELECT @id1s=@id1s, @id2s=@id2s,@dates = @dates UPDATE A SET terminate_time = B.dt FROM [Table] A,( SELECT id1 = CONVERT(int, Desk_id.value), id2 = CONVERT(int, room_id.value), dt = CONVERT(datetime, terminate_time.value) FROM dbo.f_splitstr(@id1s) Desk_id, dbo.f_splitstr(@id2s) room_id, dbo.f_splitstr(@dates) terminate_time WHERE Desk_id.id = room_id.id AND Desk_id.id = terminate_time.id ) B WHERE A.Desk_id = B.ID1 AND A.room_id = B.ID2 GO
这个还用到一个函数f_splitstr
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