如何使用OPENJSON动态加载SQL表？存储过程调试求助

一、待插入的JSON数据 #

[
    {
        "year": 2021,
        "state": "Nebraska",
        "report": "Farm Business Balance Sheet",
        "farmtype": "All Farms",
        "category": "Operator Age",
        "category_value": "45 to 54 years old",
        "category2": "All Farms",
        "category2_value": "TOTAL",
        "variable_id": "kount",
        "variable_name": "Farms",
        "variable_sequence": 10,
        "variable_level": 1,
        "variable_group": null,
        "variable_group_id": null,
        "variable_unit": "Number",
        "variable_description": "Estimated number of farms.",
        "variable_is_invalid": false,
        "estimate": 5889,
        "median": null,
        "statistic": "TOTAL",
        "rse": 0,
        "unreliable_estimate": 0,
        "decimal_display": 0
    },
    {
        "year": 2021,
        "state": "Nebraska",
        "report": "Farm Business Balance Sheet",
        "farmtype": "All Farms",
        "category": "Farm Typology",
        "category_value": "Off-farm occupation farms (2011 to present)",
        "category2": "All Farms",
        "category2_value": "TOTAL",
        "variable_id": "kount",
        "variable_name": "Farms",
        "variable_sequence": 10,
        "variable_level": 1,
        "variable_group": null,
        "variable_group_id": null,
        "variable_unit": "Number",
        "variable_description": "Estimated number of farms.",
        "variable_is_invalid": false,
        "estimate": 13398,
        "median": null,
        "statistic": "TOTAL",
        "rse": 0,
        "unreliable_estimate": 0,
        "decimal_display": 0
    }
]

二、现有存储过程代码 #

CREATE PROCEDURE load_proc 
@json NVARCHAR(MAX),
@table_name VARCHAR(50)
AS 
begin
declare @sql varchar(max)
set @sql= 'INSERT INTO [dbo].' + QUOTENAME(@table_name) + '(year_, state_,
report,farmtype,category,category_value,category2,category2_value,
variable_id,variable_name,variable_sequence,variable_level,
variable_group,variable_group_id,variable_unit,
variable_description,variable_is_invalid,estimate,median,statistic,
rse,unreliable_estimate,decimal_display) SELECT year_,
state_,
report,
farmtype,
category,
category_value,
category2,
category2_value,
variable_id,
variable_name,
variable_sequence,
variable_level,
variable_group,
variable_group_id,
variable_unit,
variable_description,
variable_is_invalid,
estimate,
median,
statistic,
rse,
unreliable_estimate,
decimal_display
        FROM OPENJSON('''+ @json +''',' + '''$'')
            WITH (
            year_ varchar(255) ' + '''$.year'',
state_ varchar(255) ' + '''$.state'',
report varchar(255) ' + '''$.report'',
farmtype varchar(255) ' + '''$.farmtype'',
category varchar(255) ' + '''$.category'',
category_value int ' + '''$.category_value'',
category2 varchar(255) ' + '''$.category2'',
category2_value int ' + '''$.category2_value'',
variable_id varchar(255) ' + '''$.variable_id'',
variable_name varchar(255) ' + '''$.variable_name'',
variable_sequence varchar(255) ' + '''$.variable_sequence'',
variable_level varchar(255) ' + '''$.variable_level'',
variable_group varchar(255) ' + '''$.variable_group'',
variable_group_id varchar(255) ' + '''$.variable_group_id'',
variable_unit varchar(255) ' + '''$.variable_unit'',
variable_description varchar(255) ' + '''$.variable_description'',
variable_is_invalid varchar(255) ' + '''$.variable_is_invalid'',
estimate int ' + '''$.estimate'',
median int ' + '''$.median'',
statistic varchar(255) ' + '''$.statistic'',
rse int ' + '''$.rse'',
unreliable_estimate int ' + '''$.unreliable_estimate'',
decimal_display int ' + '''$.decimal_display'')'
execute(@sql)
end

三、调试查询及结果 #

调试查询1 #

SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues

结果1 #

调试查询2 #

select * from openjson(@json)

结果2 #

四、问题分析与修正方案 #

核心问题 #

1. 数据类型不匹配：现有代码将category_value、category2_value定义为int，但JSON中这两个字段是字符串值（如"45 to 54 years old"），会导致转换失败；variable_is_invalid是布尔值却定义为varchar，同样存在类型不兼容问题。
2. 动态SQL拼接错误：手动拼接JSON字符串时容易出现转义错误，且部分数值型字段（如variable_sequence）被错误定义为字符串类型。
3. 冗余路径参数：OPENJSON处理顶层数组时，'$'路径参数是多余的，且拼接方式会引发语法问题。

修正后的存储过程 #

CREATE PROCEDURE load_proc 
    @json NVARCHAR(MAX),
    @table_name VARCHAR(50)
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX);

    -- 使用参数化动态SQL避免转义错误与SQL注入
    SET @sql = N'INSERT INTO [dbo].' + QUOTENAME(@table_name) + N'(
        year_, state_, report, farmtype, category, category_value,
        category2, category2_value, variable_id, variable_name,
        variable_sequence, variable_level, variable_group,
        variable_group_id, variable_unit, variable_description,
        variable_is_invalid, estimate, median, statistic, rse,
        unreliable_estimate, decimal_display
    )
    SELECT 
        year_, state_, report, farmtype, category, category_value,
        category2, category2_value, variable_id, variable_name,
        variable_sequence, variable_level, variable_group,
        variable_group_id, variable_unit, variable_description,
        variable_is_invalid, estimate, median, statistic, rse,
        unreliable_estimate, decimal_display
    FROM OPENJSON(@json)
    WITH (
        year_ INT ''$.year'',
        state_ VARCHAR(255) ''$.state'',
        report VARCHAR(255) ''$.report'',
        farmtype VARCHAR(255) ''$.farmtype'',
        category VARCHAR(255) ''$.category'',
        category_value VARCHAR(255) ''$.category_value'',
        category2 VARCHAR(255) ''$.category2'',
        category2_value VARCHAR(255) ''$.category2_value'',
        variable_id VARCHAR(255) ''$.variable_id'',
        variable_name VARCHAR(255) ''$.variable_name'',
        variable_sequence INT ''$.variable_sequence'',
        variable_level INT ''$.variable_level'',
        variable_group VARCHAR(255) ''$.variable_group'',
        variable_group_id VARCHAR(255) ''$.variable_group_id'',
        variable_unit VARCHAR(255) ''$.variable_unit'',
        variable_description VARCHAR(255) ''$.variable_description'',
        variable_is_invalid BIT ''$.variable_is_invalid'',
        estimate INT ''$.estimate'',
        median INT ''$.median'',
        statistic VARCHAR(255) ''$.statistic'',
        rse INT ''$.rse'',
        unreliable_estimate INT ''$.unreliable_estimate'',
        decimal_display INT ''$.decimal_display''
    )';

    EXEC sp_executesql @sql, N'@json NVARCHAR(MAX)', @json = @json;
END

关键修正说明 #

• 字段类型对齐：将category_value、category2_value改为VARCHAR(255)匹配字符串值；variable_sequence、variable_level改为INT；variable_is_invalid改为BIT对应JSON布尔值。
• 参数化动态SQL：用sp_executesql传递@json参数，彻底避免手动拼接JSON的转义错误，同时提升安全性。
• 简化语法：去掉OPENJSON冗余的'$'路径参数，默认解析顶层数组即可。

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