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

You can not mix query with flow control, if you use not dynamic SQL so first get the cid check if it is 0 and then add to both queries the with caulse or make a new string which gets ececuted — nbk, Jul 29 '23 at 16:12
IF ((SELECT CID FROM #updateJsonRequest) > 0) is the issue. why not skip that nonsense altogether and just do UPDATE ... WHERE CID > 0... INSERT WHERE CID = 0 — siggemannen, Jul 29 '23 at 21:15

score 0 · Accepted Answer · answered Jul 30 '23 at 02:00

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.

Dynamic insert/update json array request to database

1 Answers1