SQL Server使用OPENJSON处理动态JSON列存入数据库表报错如何解决

报错原因 #

• 报错1「必须声明表变量 "@json"」：EXEC()执行的动态SQL运行在独立作用域，外部声明的@json变量无法直接在动态SQL内部访问；同时原代码OPENJSON((SELECT * FROM @json)写法错误，@json是字符串类型变量而非表变量，无需通过SELECT *读取；且你传入的JSON根节点是数组，不存在$.records层级，路径配置错误。
• 报错2「关键字 'with' 附近有语法错误」：拼接字段schema时漏写了JSON路径的闭合单引号，生成的动态SQL中每个字段的路径字符串末尾缺少单引号，触发语法解析错误。

修复后可运行代码 #

--- JSON document --- 
DECLARE @json nvarchar(max) = N'
[    
    {
    "ProductId":1,
    "ProductName":"Food",
    "ProductDescription":"Apple",
    "DatePurchased":"1995-05-01T00:00:00"
    },   
    {
    "ProductId":2,
    "ProductName":"Electronics",
    "ProductDescription":"TV",
    "DatePurchased":"2018-09-17T00:00:00"
    }
]   
'
-- 从JSON中选取键值
SELECT [Key] 
FROM OPENJSON(@json, '$')

DECLARE @columns varchar(max) = N''
DECLARE @schema varchar(max) = N''
DECLARE @stm nvarchar(max) -- 推荐用nvarchar存动态SQL
DECLARE @param_def nvarchar(max) = N'@json_in nvarchar(max)' -- 定义动态SQL入参

-- 列信息预处理，补全路径的闭合单引号
SELECT 
   @columns = CONCAT(@columns, ',', QUOTENAME([key])),
   @schema = CONCAT(@schema, ',', QUOTENAME([key]),' varchar(max) ''$.', [key], '''') -- 末尾补两个单引号，转义后生成闭合单引号
FROM OPENJSON(@json, '$[0]')

DROP TABLE IF EXISTS #TestData

-- 拼接语句，修正JSON路径、变量传参方式
SET @stm = 'SELECT '+ 
       STUFF(@columns, 1, 1, '') + ' INTO #TestData FROM OPENJSON(@json_in, ''$'') WITH (' + STUFF(@schema, 1, 1, '') + '); SELECT * FROM #TestData' -- 这里加SELECT可直接查看结果，不需要可以删除

-- 用sp_executesql传参，把外部@json传入动态SQL
EXEC sp_executesql @stm, @param_def, @json_in = @json

补充说明 #

如果需要在动态SQL外部访问生成的#TestData表，可以将临时表改为全局临时表##TestData，或者提前在外部创建好表结构再通过动态SQL插入数据。

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