SQL Server动态JSON数组批量插入/更新存储过程报错求助

报错原因分析 #

你的存储过程报错是因为IF ((SELECT CID FROM #updateJsonRequest) > 0)这行代码：当JSON数组包含多条记录时，这个子查询会返回多个CID值，SQL Server不允许将多值结果直接用于>这类比较运算，因此抛出"Subquery returned more than 1 value"错误。

修正后的存储过程 #

核心思路是分别处理临时表中需要更新和插入的数据集，不需要用IF ELSE判断整个临时表，而是通过WHERE条件筛选出对应记录：

CREATE PROCEDURE [dbo].[UpdateJsonData] 
    @updateRequest NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 将JSON解析到临时表
    SELECT * 
    INTO #updateJsonRequest
    FROM OPENJSON(@updateRequest)
    WITH (
        CID int '$.CID',
        TID int '$.TID',
        CrID int '$.CrID',
        Op nvarchar(10) '$.Op',         
        IsAct bit '$.IsAct',       
        Modified nvarchar(20) '$.Modified'
    )
         
    BEGIN TRANSACTION
    BEGIN TRY
        -- 1. 更新CID>0的记录
        UPDATE WF
        SET 
            [TID] = TEMPWF.[TID],
            [CrID] = TEMPWF.[CrID],
            [Op] = TEMPWF.[Op],                         
            [IsAct] = TEMPWF.[IsAct],
            [Modified] = TEMPWF.[Modified]
        FROM [dbo].[update] AS WF
        INNER JOIN #updateJsonRequest AS TEMPWF 
            ON WF.CID = TEMPWF.CID
        WHERE TEMPWF.CID > 0; -- 只更新CID大于0的记录

        -- 2. 插入CID=0的记录
        -- 注意：原插入语句中的Created字段临时表未包含，这里假设用当前时间，若前端传入需调整WITH子句添加该字段
        INSERT INTO [dbo].[update] ([TID], [CrID], [Op], [IsAct], [Created])
        SELECT 
            TID, 
            [CrID], 
            [Op], 
            [IsAct], 
            GETDATE() -- 替换为实际的Created值来源，比如前端传入则改为TEMPWF.Created
        FROM #updateJsonRequest
        WHERE CID = 0; -- 只插入CID等于0的记录

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        -- 事务回滚并抛出错误
        IF (@@TRANCOUNT > 0)
            ROLLBACK TRANSACTION;
        THROW; -- 抛出捕获到的错误信息
    END CATCH

    -- 清理临时表
    DROP TABLE IF EXISTS #updateJsonRequest;
END

关键修正点说明 #

1. 移除错误的IF ELSE判断：直接通过WHERE TEMPWF.CID > 0和WHERE CID = 0筛选需要更新/插入的记录，批量处理所有符合条件的数据。
2. 完善异常处理：CATCH块中添加事务回滚和错误抛出，保证数据一致性，同时便于排查问题。
3. 修正插入字段问题：原代码中插入语句的Created字段在临时表中不存在，这里用GETDATE()作为默认值，若该字段由前端传入，需要在OPENJSON的WITH子句中添加Created datetime '$.Created'。

调用示例 #

确保传入的JSON参数为Unicode字符串（前缀N）：

DECLARE @json NVARCHAR(MAX) = N'[
  {
    "CID": 8,
    "TID": 1017,
    "CrID": 1,
    "Op": "or",
    "IsAct": "true",
    "Modified": "T"
  },
  {
    "CID": 9,
    "TID": 1017,
    "CrID": 1,
    "Op": "-",
    "IsAct": "true",
    "Modified": "T"
  },
  {
    "CID": 0,
    "TID": 1017,
    "CrID": 1,
    "Op": "and",
    "IsAct": "true",
    "Modified": "T"
  }
]';

EXEC [dbo].[UpdateJsonData] @json;

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