Dynamic insert/update json array request to database

Question

I have question about how to update/insert Json value into SQL Server. My logic was: frontend sends dynamic Json array value to me i.e they send Json array value (it have both insert and update value).

This is a sample Json:

[
  {
    "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"
  }
]

If column CID has a value, I need to update the database; if CID value is 0, I want to insert into the database.

Below is my StoredProcedure but it is not working.It shows below Error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

CREATE PROCEDURE [dbo].[Update] 
    @updateRequest NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    
    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
        IF ((SELECT CID FROM #updateJsonRequest) > 0)
        BEGIN                
            UPDATE [dbo].[update]
            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
        END     
        ELSE
        BEGIN
            INSERT INTO [dbo].[update] ([TID], [CrID], [Op], [IsAct], [Created])
            (SELECT TID, [CrID], [Op], [IsAct], [Created] 
             FROM #updateJsonRequest)
        END

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF (@@TRANCOUNT > 0)                        
              
    END CATCH
END

and I'm using this procedure and sampleJson as follows

[dbo].[Update] sampleJson

Please kindly help me. I want to insert and update happen to database in the same stored procedure

Answer 1

Your subquery doesn't make much sense. It seems you actually want to just do a joined update.

So either use a MERGE:

CREATE OR ALTER PROCEDURE [dbo].[Update] 
    @updateRequest NVARCHAR(MAX)
AS

SET NOCOUNT, XACT_ABORT ON;

WITH updateJsonRequest AS (
    SELECT *
    FROM OPENJSON(@updateRequest)
    WITH (
      CID int,
      TID int,
      CrID int,
      Op nvarchar(10),         
      IsAct bit,       
      Modified nvarchar(20)
    )
)
MERGE dbo.[update] WITH (HOLDLOCK, UPDLOCK) AS u
USING updateJsonRequest AS s
  ON s.CID = u.CID
WHEN MATCHED THEN
  UPDATE SET
    TID = s.TID,
    CrID = s.CrID,
    Op = s.Op,               
    IsAct = s.IsAct,
    Modified = s.Modified
WHEN NOT MATCHED THEN
  INSERT
    (TID, CrID, Op, IsAct, Created)
  VALUES
    (s.TID, s.CrID, s.Op, s.IsAct, s.Created)
;

Or a combination of INSERT and UPDATE:

CREATE OR ALTER PROCEDURE [dbo].[Update] 
    @updateRequest NVARCHAR(MAX)
AS

SET NOCOUNT, XACT_ABORT ON;

BEGIN TRAN;

WITH updateJsonRequest AS (
    SELECT *
    FROM OPENJSON(@updateRequest)
    WITH (
      CID int,
      TID int,
      CrID int,
      Op nvarchar(10),         
      IsAct bit,       
      Modified nvarchar(20)
    )
)
UPDATE u
SET TID = s.TID,
    CrID = s.CrID,
    Op = s.Op,               
    IsAct = s.IsAct,
    Modified = s.Modified
FROM dbo.[update] WITH (HOLDLOCK, UPDLOCK) AS u
JOIN updateJsonRequest AS s
  ON s.CID = u.CID;

WITH updateJsonRequest AS (
    SELECT *
    FROM OPENJSON(@updateRequest)
    WITH (
      CID int,
      TID int,
      CrID int,
      Op nvarchar(10),         
      IsAct bit,       
      Modified nvarchar(20)
    )
)
INSERT dbo.[update]
    (TID, CrID, Op, IsAct, Created)
SELECT
    s.TID, s.CrID, s.Op, s.IsAct, s.Created
FROM updateJsonRequest s
WHERE NOT EXISTS (SELECT 1
    FROM dbo.[update] u
    WHERE u.CID = s.CID
);

COMMIT;

Note the use of locking hints HOLDLOCK, UPDLOCK to ensure consistency.

Note also the use of XACT_ABORT ON and the removal of the poor error handling, as XACT_ABORT should handle all rollbacks correctly anyway.