使用存储过程实现循环更新3组数组中的数据

传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