SQL Server預存程序 對數組參數的迴圈處理

方法一 分割

例:通過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