如何在SQL存储过程中基于输入参数优雅生成JSON对象？

我需要在SQL存储过程中基于输入参数创建一个JSON对象。目前用拼接字符串的方式可行，但写法繁琐不够优雅，想找更优的实现方案。

当前实现代码：

-- adding log entry
declare @logjson nvarchar(max); 

set @logjson = concat(       '{"InputParameters":[', '{');
set @logjson = concat(@logjson, '"MacOwner":"', @Owner, '",');
set @logjson = concat(@logjson, '"EMS":"', @EMS, '",');
set @logjson = concat(@logjson, '"AXBomId":"', @AXBomId, '",');
set @logjson = concat(@logjson, '"Serial":"', @SerialId, '",');
set @logjson = concat(@logjson, '"Device":"', @AXItem, '",');
set @logjson = concat(@logjson, '"HWDevice":"', @HWDevice, '",');
set @logjson = concat(@logjson, '"TestPCHostName":"', @TestPCHostName, '",');
set @logjson = concat(@logjson, '"TestSWVersion":"', @TestSWVersion, '"');  --Last parameter no comma
set @logjson = concat(@logjson, '}],');
set @logjson = concat(@logjson, '"OutputParameters":[','{');
set @logjson = concat(@logjson, '"MACAddress":"', @MACAddress, '"');
set @logjson = concat(@logjson, '}');
set @logjson = concat(@logjson, ']' );
set @logjson = concat(@logjson, '}');

我试过用SELECT xxxxx FOR JSON AUTO语句，但误以为该语句必须查询数据表才能使用。

方案1：使用FOR JSON PATH构造嵌套JSON（无需数据表） #

SQL Server的FOR JSON PATH支持直接从变量生成JSON，不需要依赖实际数据表。通过嵌套子查询可以完美匹配你需要的结构：

DECLARE @logjson NVARCHAR(MAX);

SELECT 
    -- 生成InputParameters数组（包含单个对象）
    (SELECT 
         @Owner AS MacOwner,
         @EMS AS EMS,
         @AXBomId AS AXBomId,
         @SerialId AS Serial,
         @AXItem AS Device,
         @HWDevice AS HWDevice,
         @TestPCHostName AS TestPCHostName,
         @TestSWVersion AS TestSWVersion
     FOR JSON PATH) AS InputParameters,
    -- 生成OutputParameters数组（包含单个对象）
    (SELECT @MACAddress AS MACAddress 
     FOR JSON PATH) AS OutputParameters
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

• FOR JSON PATH：自动将查询结果转为JSON格式，支持嵌套结构
• WITHOUT_ARRAY_WRAPPER：去掉最外层的数组包裹，让整体成为一个JSON对象
• 该方式会自动处理字符串转义（比如参数值中的双引号、特殊字符），避免手动拼接的格式错误

方案2：用JSON_OBJECT+JSON_ARRAY直接构建（SQL Server 2022+） #

如果你的SQL Server版本是2022及以上，可以用更直观的JSON构造函数，语法完全贴合JSON结构：

DECLARE @logjson NVARCHAR(MAX);

SET @logjson = JSON_OBJECT(
    'InputParameters': JSON_ARRAY(
        JSON_OBJECT(
            'MacOwner': @Owner,
            'EMS': @EMS,
            'AXBomId': @AXBomId,
            'Serial': @SerialId,
            'Device': @AXItem,
            'HWDevice': @HWDevice,
            'TestPCHostName': @TestPCHostName,
            'TestSWVersion': @TestSWVersion
        )
    ),
    'OutputParameters': JSON_ARRAY(
        JSON_OBJECT('MACAddress': @MACAddress)
    )
);

这种写法可读性极强，新增或修改参数时只需调整对应键值对，维护成本更低。

原实现的问题 #

• 手动拼接需要处理引号转义、末尾逗号等细节，极易出错
• 代码冗长，参数变动时需修改多处
• 无法自动处理特殊字符，可能导致JSON格式损坏

内容的提问来源于stack exchange，提问作者Søren Graversen