SQL Server 批次刪除存儲過程：動態 SQL 解決 IN 子句參數化難題

問題背景

嘗試使用參數化查詢執行批次刪除時會遇到型別轉換錯誤：

-- 錯誤寫法
CREATE PROC up_del @ID NVARCHAR(20)
AS
DELETE FROM table WHERE id IN (@ID);
-- 執行 up_del '1,2,3' 會報錯：varchar 轉 int 失敗

動態 SQL 解決方案

透過字串拼接構建動態 SQL 陳述式：

CREATE PROCEDURE [dbo].[UP_tb_Delete] 
    @ID NVARCHAR(50)
AS
BEGIN
    DECLARE @Sql NVARCHAR(200);
    SET @Sql = 'DELETE FROM tb_zazhi WHERE ID IN (' + @ID + ')';
    EXEC (@Sql);
END

執行範例

EXEC UP_tb_Delete '1,2,3,4,5'; -- 正確刪除 ID 為 1,2,3,4,5 的記錄

安全性考量與改進

字串分割函數範例 (相容性較好)

CREATE FUNCTION dbo.SplitString (@Input NVARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @Result TABLE (Item NVARCHAR(100))
AS
BEGIN
    DECLARE @Start INT = 1, @End INT;
    WHILE @Start <= LEN(@Input)
    BEGIN
        SET @End = CHARINDEX(@Delimiter, @Input, @Start);
        IF @End = 0 SET @End = LEN(@Input) + 1;
        INSERT INTO @Result VALUES(SUBSTRING(@Input, @Start, @End - @Start));
        SET @Start = @End + 1;
    END
    RETURN;
END

-- 安全版批次刪除
CREATE PROCEDURE dbo.SafeBatchDelete @IDs NVARCHAR(MAX)
AS
DELETE FROM tb_zazhi WHERE ID IN (SELECT Item FROM dbo.SplitString(@IDs, ','));