如何在SQL存储过程的JSON路径中传入@TargetIndex参数

你的问题核心是SQL Server的OPENJSON WITH子句不支持直接在JSON路径中引用变量，所以@TargetIndex无法直接嵌入到N'$.TargetField[@TargetIndex].field'这类路径里。以下是两种可行的解决方案：

方案一：使用动态SQL拼接完整查询语句 #

通过拼接字符串生成包含索引参数的JSON路径，再执行动态SQL：

CREATE PROCEDURE YourProcedureName
    @ReferenceID int,
    @BureausID int,
    @EmployersID int, 
    @FileType varchar(12),
    @TargetIndex int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL nvarchar(max);
    -- 拼接动态SQL，将@TargetIndex代入JSON路径
    SET @SQL = N'
        SELECT 
            SalesOrderJsonData.* 
        FROM 
            MapHistories AS Tab
        CROSS APPLY 
            OPENJSON(Tab.MapJSON, N''$.Mapping'') 
                WITH 
                (
                    ReferenceNumber int N''$.ReferenceNumber'',
                    target_field nvarchar(max) N''$.TargetField[' + CAST(@TargetIndex AS nvarchar) + '].field'', 
                    output_field_description nvarchar(max) N''$.TargetField[' + CAST(@TargetIndex AS nvarchar) + '].output_field_description''
                ) AS SalesOrderJsonData
        WHERE Tab.ReferenceID = @ReferenceID AND Tab.BureausID = @BureausID AND Tab.EmployersID = @EmployersID AND Tab.FileType = @FileType
    ';
    -- 执行动态SQL，传入其他参数
    EXEC sp_executesql @SQL, 
        N'@ReferenceID int, @BureausID int, @EmployersID int, @FileType varchar(12)',
        @ReferenceID = @ReferenceID,
        @BureausID = @BureausID,
        @EmployersID = @EmployersID,
        @FileType = @FileType;
END

注意：动态SQL要注意SQL注入风险，这里@TargetIndex是int类型，转换为字符串后不会有注入问题；如果是字符串参数需要用参数化传递。

方案二：先解析TargetField数组，再按索引筛选 #

先把TargetField数组单独解析出来，筛选出指定索引的元素，再和主Mapping数据关联：

CREATE PROCEDURE YourProcedureName
    @ReferenceID int,
    @BureausID int,
    @EmployersID int, 
    @FileType varchar(12),
    @TargetIndex int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
        MappingData.ReferenceNumber,
        TargetFieldData.field AS target_field,
        TargetFieldData.output_field_description
    FROM 
        MapHistories AS Tab
    CROSS APPLY 
        OPENJSON(Tab.MapJSON, N'$.Mapping') 
            WITH 
            (
                ReferenceNumber int N'$.ReferenceNumber',
                TargetField nvarchar(max) N'$.TargetField' AS JSON -- 将TargetField作为JSON数组保留
            ) AS MappingData
    CROSS APPLY 
        OPENJSON(MappingData.TargetField)
            WITH 
            (
                field nvarchar(max) N'$.field',
                output_field_description nvarchar(max) N'$.output_field_description'
            ) AS TargetFieldData
    -- 筛选指定索引的元素（OPENJSON的默认索引从0开始）
    WHERE TargetFieldData.[key] = CAST(@TargetIndex AS nvarchar)
      AND Tab.ReferenceID = @ReferenceID 
      AND Tab.BureausID = @BureausID 
      AND Tab.EmployersID = @EmployersID 
      AND Tab.FileType = @FileType;
END

这个方案不需要动态SQL，更安全，适合需要避免动态SQL的场景。注意[key]是OPENJSON解析数组时返回的默认索引列，类型为nvarchar，所以需要和@TargetIndex的字符串形式匹配。

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