SQL Server 查詢結果轉 JSON 格式：SerializeJSON 預存程序完整原始碼與使用範例

提供一個可直接部署的 SerializeJSON 預存程序，接受任意 SELECT 陳述式字串，透過 FOR XML RAW 產生中繼 XML，再以 T-SQL 字串剖析轉換為 JSON 陣列格式，適用於 SQL Server 2016 前無原生 FOR JSON 的環境。

預存程序完整原始碼

SET ANSI_NULLS ON; GO
SET QUOTED_IDENTIFIER ON; GO

CREATE PROCEDURE [dbo].[SerializeJSON] 
    @ParameterSQL AS VARCHAR(MAX)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX), @XMLString VARCHAR(MAX), @XML XML, @ParamList NVARCHAR(1000) = N'@XML XML OUTPUT';
    
    -- 1. 動態 SQL 執行查詢並取得 XML 結果
    SET @SQL = N'WITH PrepareTable (XMLString) AS (' + @ParameterSQL + N' FOR XML RAW, TYPE, ELEMENTS) SELECT @XML = [XMLString] FROM [PrepareTable]';
    EXEC sp_executesql @SQL, @ParamList, @XML = @XML OUTPUT;
    SET @XMLString = CAST(@XML AS VARCHAR(MAX));

    -- 2. 字串剖析 XML 轉 JSON
    DECLARE @JSON VARCHAR(MAX) = '', @Row VARCHAR(MAX);
    DECLARE @RowStart INT, @RowEnd INT, @FieldStart INT, @FieldEnd INT;
    DECLARE @Key VARCHAR(MAX), @Value VARCHAR(MAX);
    DECLARE @StartRoot VARCHAR(100) = '', @EndRoot VARCHAR(100) = '';
    DECLARE @StartField VARCHAR(100) = '<', @EndField VARCHAR(100) = '>';

    SET @RowStart = CHARINDEX(@StartRoot, @XMLString, 0);
    WHILE @RowStart > 0 BEGIN
        SET @RowStart += LEN(@StartRoot);
        SET @RowEnd = CHARINDEX(@EndRoot, @XMLString, @RowStart);
        SET @Row = SUBSTRING(@XMLString, @RowStart, @RowEnd - @RowStart);
        SET @JSON += '{';

        SET @FieldStart = CHARINDEX(@StartField, @Row, 0);
        WHILE @FieldStart > 0 BEGIN
            SET @FieldStart += LEN(@StartField);
            SET @FieldEnd = CHARINDEX(@EndField, @Row, @FieldStart);
            SET @Key = SUBSTRING(@Row, @FieldStart, @FieldEnd - @FieldStart);
            SET @JSON += '"' + @Key + '":';

            SET @FieldStart = @FieldEnd + 1;
            SET @FieldEnd = CHARINDEX(' 0 SET @JSON = SUBSTRING(@JSON, 1, LEN(@JSON) - 1);  -- 移除尾端逗號
        SET @JSON += '},';

        SET @RowStart = CHARINDEX(@StartRoot, @XMLString, @RowEnd);
    END
    IF LEN(@JSON) > 0 SET @JSON = SUBSTRING(@JSON, 1, LEN(@JSON) - 1);  -- 移除尾端逗號
    SET @JSON = '[' + @JSON + ']';
    SELECT @JSON;
END; GO

使用方法

EXEC [SerializeJSON] 'SELECT * FROM [Employee_TBL]';

輸出格式：[{"col1":"val1","col2":"val2"},{"col1":"val3","col2":"val4"}]

已知限制與注意事項

• 資料型別皆輸出為字串：JSON 無型別資訊，數值/日期均以字串呈現。
• 特殊字元未跳脫：值中含 "、\、控制字元會產生無效 JSON，需自行增補 REPLACE 跳脫邏輯。
• NULL 值處理：FOR XML ELEMENTS 預設略過 NULL 欄位，導致 Key 缺失；建議查詢端用 ISNULL(col, '') 或 ELEMENTS XSINIL。
• 效能：大量資料字串操作極慢，建議單次 < 1 萬列，或改用 CLR/應用層序列化。
• 動態 SQL 注入風險：@ParameterSQL 直接拼接，呼叫端必須確保輸入安全（內部工具可接受，對外服務嚴禁）。

現代替代方案（SQL Server 2016+ / Azure SQL Database）

-- 原生 FOR JSON，效能優、型別保留、標準跳脫
SELECT * FROM Employee_TBL FOR JSON PATH, ROOT('Data');

-- 指定根名稱、輸出陣列
SELECT * FROM Employee_TBL FOR JSON AUTO;

**強烈建議**：支援版本請直接使用 FOR JSON，僅在舊版本維護專案時使用本預存程序。