方法一 分割

 

例:通过SQL Server存储过程传送数组参数删除多条记录

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 = @datesUPDATE A SET terminate_time = B.dtFROM  [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_timeWHERE Desk_id.id = room_id.id    AND Desk_id.id = terminate_time.id) BWHERE A.Desk_id = B.ID1 AND A.room_id = B.ID2GO

这个还用到一个函数f_splitstr

CREATE FUNCTION dbo.f_splitstr(    @str varchar(8000))RETURNS @r TABLE(id int IDENTITY(1, 1), value varchar(5000))ASBEGIN    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)    RETURNEND

 

 

方法三 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