SQL Server存储过程 对数组参数的循环处理

hmzone

　　方法一 分割
　　例:通过SQL Server存储过程传送数组参数删除多条记录
　　eg. ID 值为'1,2,3' 以下存储过程就是删除表中id号为1,2,3的记录：
　　CREATE PROCEDURE DeleteNews
    @ID nvarchar(500)
as
    DECLARE @PointerPrev int
    DECLARE @PointerCurr int
    DECLARE @TId int
    Set @PointerPrev=1
     
    while (@PointerPrev < LEN(@ID))
    Begin
        Set @PointerCurr=CharIndex(',',@ID,@PointerPrev)
        if(@PointerCurr>0)
        Begin
            set @TId=cast(SUBSTRING(@ID,@PointerPrev,@PointerCurr-@PointerPrev) as int)
            Delete from News where ID=@TID
            SET @PointerPrev = @PointerCurr+1
        End
        else
            Break
    End
    --删除最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再删除
     set @TId=cast(SUBSTRING(@ID,@PointerPrev,LEN(@ID)-@PointerPrev+1) as int)
     Delete from News where ID=@TID
GO
　　
　　方法二 Table对象
　　传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

　　
　　方法三 xml
　　
　　应该用SQL2000 OpenXML更简单，效率更高，代码更可读：
　　CREATE Procedure [dbo].[ProductListUpdateSpecialList]
(
@ProductId_Array NVARCHAR(2000),
@ModuleId INT
)
　　AS
　　delete from ProductListSpecial where ModuleId=@ModuleId
　　-- If empty, return
IF (@ProductId_Array IS NULL OR LEN(LTRIM(RTRIM(@ProductId_Array))) = 0)
RETURN
　　DECLARE @idoc int
　　EXEC sp_xml_preparedocument @idoc OUTPUT, @ProductId_Array
　　Insert into ProductListSpecial (ModuleId,ProductId)
Select
@ModuleId,C.[ProductId]
FROM
OPENXML(@idoc, '/Products/Product', 3)
with (ProductId int ) as C
where
C.[ProductId] is not null
　　EXEC sp_xml_removedocument @idoc