如何将SQL中JSON格式的字符串列值存入存储过程变量？

首先注意你提供的JSON存在格式错误：第三个对象里的"probability": 0.0.3是无效数值，需要修正为合法格式（比如0.3），否则JSON解析会失败。以下是针对修正后JSON的实现方案：

实现步骤 #

1. 拆分JSON数组并标记行号：用OPENJSON函数解析JSON数组，提取每个对象的name和数值字段，同时用ROW_NUMBER()生成行号，方便后续将行数据转为指定列。
2. 行转列处理：通过PIVOT将行数据转换为name1、val1、name2、val2这类目标列。
3. 赋值到变量：将行转列后的结果直接赋值给对应的变量。

完整代码示例 #

-- 示例数据（已修正JSON格式错误）
DECLARE @sample varchar(500) = '[{"name": "abc", "val": 0.90}, {"name": "def", "val": 0.22}, {"name": "ghi", "val": 0.3}]'

-- 声明需要的变量
DECLARE @name1 varchar(50), @val1 decimal(5,2)
DECLARE @name2 varchar(50), @val2 decimal(5,2)
DECLARE @name3 varchar(50), @val3 decimal(5,2)

-- 解析JSON并转列，赋值到变量
SELECT 
    @name1 = [name_1], @val1 = [val_1],
    @name2 = [name_2], @val2 = [val_2],
    @name3 = [name_3], @val3 = [val_3]
FROM (
    -- 拆分JSON数组，生成带行号的name和val行数据
    SELECT 
        CONCAT('name_', ROW_NUMBER() OVER(ORDER BY (SELECT NULL))) AS col_key,
        name AS col_value
    FROM OPENJSON(@sample)
    WITH (name varchar(50) '$.name', val decimal(5,2) '$.val')
    UNION ALL
    SELECT 
        CONCAT('val_', ROW_NUMBER() OVER(ORDER BY (SELECT NULL))) AS col_key,
        CAST(val AS varchar(50)) AS col_value
    FROM OPENJSON(@sample)
    WITH (name varchar(50) '$.name', val decimal(5,2) '$.val')
) AS src
PIVOT (
    MAX(col_value)
    FOR col_key IN ([name_1], [val_1], [name_2], [val_2], [name_3], [val_3])
) AS pivot_result

-- 验证变量赋值结果
SELECT @name1, @val1, @name2, @val2, @name3, @val3

兼容probability字段的调整 #

如果需要保留原JSON中第三个对象的probability字段，可以修改OPENJSON的映射规则，自动取val或probability的有效值：

-- 在拆分JSON的子查询中调整数值字段的提取逻辑
SELECT 
    CONCAT('name_', ROW_NUMBER() OVER(ORDER BY (SELECT NULL))) AS col_key,
    name AS col_value
FROM OPENJSON(@sample)
WITH (
    name varchar(50) '$.name',
    val decimal(5,2) '$.val',
    probability decimal(5,2) '$.probability'
)
UNION ALL
SELECT 
    CONCAT('val_', ROW_NUMBER() OVER(ORDER BY (SELECT NULL))) AS col_key,
    CAST(ISNULL(val, probability) AS varchar(50)) AS col_value
FROM OPENJSON(@sample)
WITH (
    name varchar(50) '$.name',
    val decimal(5,2) '$.val',
    probability decimal(5,2) '$.probability'
)

内容的提问来源于stack exchange，提问作者user14806829