SQL Server 預存程序：分割逗號字串並批次插入資料表

需求

傳入字串參數 '1,2,3'，需將其分割為三個值，分別插入資料表產生三筆記錄。

解決方案：自訂資料表值函數

建立一個回傳資料表型別的函數 func_splitstring，再於預存程序中 CROSS APPLY 或 INSERT ... SELECT 使用。

分割函數完整定義

CREATE FUNCTION dbo.func_splitstring 
(
    @str NVARCHAR(MAX),
    @split VARCHAR(10)
)
RETURNS @t TABLE (val VARCHAR(100))
AS
BEGIN
    DECLARE @i INT, @s INT;
    SET @i = 1; SET @s = 1;
    
    WHILE (@i > 0)
    BEGIN
        SET @i = CHARINDEX(@split, @str, @s);
        IF (@i > 0)
        BEGIN
            INSERT @t(val) VALUES(SUBSTRING(@str, @s, @i - @s));
        END
        ELSE
        BEGIN
            INSERT @t(val) VALUES(SUBSTRING(@str, @s, LEN(@str) - @s + 1));
        END
        SET @s = @i + 1;
    END
    RETURN;
END

預存程序使用範例

CREATE PROCEDURE dbo.usp_InsertSplitValues
    @csv NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO TargetTable (ColumnName)
    SELECT val FROM dbo.func_splitstring(@csv, ',');
END

直接查詢測試

SELECT val FROM dbo.func_splitstring('1,2,3', ',');
-- 結果：
-- val
-- ----
-- 1
-- 2
-- 3

進階替代方案

1. STRING_SPLIT (SQL Server 2016+)

SELECT value FROM STRING_SPLIT('1,2,3', ',');
-- 注意：不保證順序，無索引欄位

2. JSON 解析 (SQL Server 2016+)

SELECT [value] FROM OPENJSON('["1","2","3"]');
-- 保證順序，支援型別轉換

3. XML 解析 (舊版本相容)

DECLARE @xml XML = '';
SELECT t.c.value('.', 'VARCHAR(100)') 
FROM @xml.nodes('/r') t(c);

效能與選擇建議

總結

SQL Server 2016 以上建議優先使用 OPENJSON（需轉為 JSON 陣列格式）或 STRING_SPLIT（不需順序）；舊版本或需完整控制邏輯時，自訂資料表值函數是標準做法。