SQL Server 存儲過程實戰：清空表數據並批量重新插入 (含事務與錯誤處理)

業務場景

根據傳入的鍵值刪除指定表中對應記錄，然後解析分隔符字符串批量插入新數據。全過程需在事務中完成，任一失敗全量回滾。

存儲過程完整代碼

ALTER PROCEDURE [dbo].[sp_add_Jurisdiction]
    @CTableName      VARCHAR(20),   -- 目標表名
    @filedkeyValue   VARCHAR(20),   -- 刪除條件鍵值
    @filedName       VARCHAR(20),   -- 刪除條件字段名
    @filedNameAdd1   VARCHAR(20),   -- 插入字段 1
    @filedNameAdd2   VARCHAR(20),   -- 插入字段 2
    @sql             VARCHAR(6000)  -- 插入數據字符串，格式：'值1,值2;值3,值4;...'
AS
BEGIN
    DECLARE @strSQL_1 VARCHAR(6000),  -- 刪除語句
            @strSQL_2 VARCHAR(6000);  -- 插入語句

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 1. 動態構建並執行刪除語句
        SET @strSQL_1 = 'DELETE FROM [' + @CTableName + '] WHERE ' + @filedName + ' = ' + @filedkeyValue;
        EXEC(@strSQL_1);

        -- 2. 解析 @sql 字符串並循環插入
        -- 格式約定：分號(;)分隔記錄，逗號(,)分隔字段值
        DECLARE @Run BIT = 1,              -- 循環標志
                @dotIndex INT,             -- 分號位置
                @doIndex INT,              -- 逗號位置
                @strValue1 VARCHAR(100),   -- 字段 1 值
                @strValue2 VARCHAR(100),   -- 字段 2 值
                @strText VARCHAR(100),     -- 單條記錄文本
                @ReturnValue INT = 0;      -- 成功插入計數

        WHILE (@Run = 1)
        BEGIN
            IF (LEN(@sql) > 0)
            BEGIN
                SET @dotIndex = CHARINDEX(';', @sql);

                IF (@dotIndex = 0)  -- 最後一條記錄 (無分號)
                BEGIN
                    SET @doIndex = CHARINDEX(',', @sql);
                    SET @strValue1 = SUBSTRING(@sql, 1, @doIndex - 1);
                    SET @strValue2 = SUBSTRING(@sql, @doIndex + 1, LEN(@sql));

                    SET @strSQL_2 = 'INSERT INTO [' + @CTableName + '] (' 
                        + @filedName + ',' + @filedNameAdd1 + ',' + @filedNameAdd2 
                        + ') VALUES (' + @filedkeyValue + ',' + @strValue1 + ',' + @strValue2 + ')';
                    EXEC(@strSQL_2);

                    SET @ReturnValue = @ReturnValue + 1;
                    SET @Run = 0;  -- 結束循環
                END
                ELSE  -- 中間記錄 (有分號)
                BEGIN
                    SET @strText = SUBSTRING(@sql, 1, @dotIndex - 1);  -- 取第一條記錄
                    SET @sql = SUBSTRING(@sql, @dotIndex + 1, LEN(@sql)); -- 剩餘字符串

                    SET @doIndex = CHARINDEX(',', @strText);
                    SET @strValue1 = SUBSTRING(@strText, 1, @doIndex - 1);
                    SET @strValue2 = SUBSTRING(@strText, @doIndex + 1, LEN(@strText));

                    SET @strSQL_2 = 'INSERT INTO [' + @CTableName + '] (' 
                        + @filedName + ',' + @filedNameAdd1 + ',' + @filedNameAdd2 
                        + ') VALUES (' + @filedkeyValue + ',' + @strValue1 + ',' + @strValue2 + ')';
                    EXEC(@strSQL_2);

                    SET @ReturnValue = @ReturnValue + 1;
                END
            END
            ELSE
            BEGIN
                SET @Run = 0;
                SET @ReturnValue = @ReturnValue + 1;
            END
        END

        COMMIT TRANSACTION;
        RETURN @ReturnValue;  -- 返回處理記錄數

    END TRY
    BEGIN CATCH
        SET @ReturnValue = 0;
        ROLLBACK TRANSACTION;
        RETURN @ReturnValue;  -- 異常返回 0
    END CATCH
END

關鍵技術點解析

1. 動態 SQL 執行 (EXEC)

由於表名、字段名作為參數傳入，必須構建字符串後用 EXEC(@sql) 執行。注意防範 SQL 注入，生產環境建議使用 sp_executesql 參數化。

2. 字符串解析邏輯

• 輸入格式：'值1,值2;值3,值4;值5,值6' (分號分行，逗號分列)
• 使用 CHARINDEX 定位分隔符，SUBSTRING 截取片段。
• 循環處理直到字符串為空。

3. 事務與錯誤處理 (TRY...CATCH)

• BEGIN TRANSACTION 開啟顯式事務。
• 成功時 COMMIT，返回插入條數。
• 異常時 ROLLBACK，返回 0。
• SQL Server 2005+ 支持 TRY...CATCH 結構化異常處理。

4. 返回值設計

• 成功：返回實際插入記錄數 (@ReturnValue)。
• 失敗/異常：返回 0。
• 調用端可據此判斷操作結果。

調用示例

-- 假設表 tb_UserRole(UID, RoleID, DeptID)
-- 刪除 UID=100 的記錄，重新插入 (100, 5, 10) 和 (100, 6, 11)
DECLARE @ret INT;
EXEC @ret = sp_add_Jurisdiction 
    'tb_UserRole', '100', 'UID', 'RoleID', 'DeptID', '5,10;6,11';
PRINT '返回值: ' + CAST(@ret AS VARCHAR);  -- 預期輸出 2

潛在風險與改進建議

現代化改寫方向 (SQL Server 2008+)

-- 使用表值參數 (TVP) 替換分隔符字符串
CREATE TYPE dbo.UDT_Jurisdiction AS TABLE (
    FiledKeyValue VARCHAR(20),
    FieldAdd1     VARCHAR(20),
    FieldAdd2     VARCHAR(20)
);
GO

CREATE PROCEDURE dbo.sp_add_Jurisdiction_V2
    @CTableName SYSNAME,
    @filedName  SYSNAME,
    @filedNameAdd1 SYSNAME,
    @filedNameAdd2 SYSNAME,
    @Data       dbo.UDT_Jurisdiction READONLY
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    BEGIN TRY
        BEGIN TRANSACTION;
        SET @sql = N'DELETE FROM ' + QUOTENAME(@CTableName) + N' WHERE ' + QUOTENAME(@filedName) + N' = @KeyVal';
        -- 需動態執行刪除，或限制表名固定後參數化
        -- 批量插入
        SET @sql = N'INSERT INTO ' + QUOTENAME(@CTableName) 
            + N'(' + QUOTENAME(@filedName) + N',' + QUOTENAME(@filedNameAdd1) + N',' + QUOTENAME(@filedNameAdd2) + N')'
            + N' SELECT @KeyVal, FieldAdd1, FieldAdd2 FROM @Data';
        EXEC sp_executesql @sql, N'@KeyVal VARCHAR(20), @Data dbo.UDT_Jurisdiction READONLY', @KeyVal, @Data;
        COMMIT TRANSACTION;
        RETURN @@ROWCOUNT;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        RETURN 0;
    END CATCH
END