SQL Server存储过程传递JSON变量参数失败问题咨询

问题场景 #

已创建如下表和存储过程：

CREATE TABLE ReportsPerFund 
(
    id INT IDENTITY(1,1) NOT NULL,
    fundId INT NULL,
    reportId INT NULL,
    PRIMARY KEY (id)
)

CREATE PROCEDURE [updateReportsPerFund] 
    @pJson nvarchar(max)
AS
BEGIN
    INSERT INTO ReportsPerFund (fundId, reportId)
        SELECT fundId, reportId
        FROM OPENJSON(@pJson, '$.reportsPerFund')
        WITH (fundId int, reportId int)
END

直接传入JSON字符串调用存储过程可正常执行：

updateReportsPerFund N'{"reportsPerFund":   [{"fundId":413, "reportId":1}]}';

但将JSON字符串赋值给nvarchar(max)变量后调用存储过程失败，且无具体错误提示。

解决步骤 #

1. 修正变量声明与赋值逻辑

最常见的问题是变量赋值时遗漏Unicode前缀N，或引号转义错误。正确的变量使用方式如下：

-- 声明变量并赋值，必须加N前缀保证Unicode编码兼容
DECLARE @jsonData nvarchar(max) = N'{"reportsPerFund": [{"fundId":413, "reportId":1}]}';

-- 调用存储过程，建议明确指定参数名避免歧义
EXEC [updateReportsPerFund] @pJson = @jsonData;

2. 新增错误捕获获取具体原因

如果仍失败，修改存储过程添加错误捕获逻辑，定位具体问题：

ALTER PROCEDURE [updateReportsPerFund] 
    @pJson nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        INSERT INTO ReportsPerFund (fundId, reportId)
        SELECT fundId, reportId
        FROM OPENJSON(@pJson, '$.reportsPerFund')
        WITH (fundId int, reportId int);
    END TRY
    BEGIN CATCH
        -- 返回错误编号、信息及行号
        SELECT 
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage,
            ERROR_LINE() AS ErrorLine;
    END CATCH
END

再次调用后，就能看到明确的错误提示，比如JSON格式非法、权限不足等。

3. 排查其他潜在问题

• 执行SELECT @jsonData检查变量内容是否与预期一致，确认赋值过程中无字符丢失或转义错误
• 确认使用的SQL Server版本为2016及以上（JSON支持的最低版本）
• 检查当前用户是否拥有ReportsPerFund表的插入权限

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