Stored procedure with multiple insert

Question

I want a value of #temp table and People table value together. I can use json_modify but it will work only for single value insert. But in case of multiple values, we can make both in JSON object which will be array of object and combine both. But how?

   CREATE PROCEDURE [dbo].[SpPersonIns]
   (@json nvarchar(max) OUTPUT)
   AS
   BEGIN
       CREATE TABLE #temp (PersonId int)
       INSERT INTO People (FirstName, MiddleName, LastName, SSN, UserPersonId)
       OUTPUT inserted.PersonId
       INTO #temp 
       SELECT p.firstName, p.middleName, p.lastName, p.ssN, p.userPersonId 
       FROM OPENJSON(@json) WITH(
           firstName varchar(100),
           middleName varchar(100),
           lastName varchar(100),
           ssN varchar(9),
           userPersonId int 
       ) as p
       declare @p nvarchar(max);
       select @p =  t.PersonId from #temp as t
       SELECT @json = JSON_MODIFY(@json,'$.PersonId',@p)
   END

Slight detour but a very important one. No matter what the rest of your code is doing you need to get that SSN encrypted immediately if not sooner. That data needs to be protected at all times, even in your temp table. — Sean Lange, Sep 15 '22 at 19:14

score 0 · Answer 1 · answered Sep 15 '22 at 23:55

You have two options.

The simplest is to just output the whole inserted and then rebuild the JSON from it

CREATE OR ALTER PROCEDURE [dbo].[SpPersonIns]
   @json nvarchar(max) OUTPUT
AS

DECLARE @temp TABLE (
    PersonId int,
    firstName varchar(100),
    middleName varchar(100),
    lastName varchar(100),
    ssN varchar(9),
    userPersonId int 
);

INSERT INTO People (FirstName, MiddleName, LastName, SSN, UserPersonId)
OUTPUT inserted.PersonId, inserted.firstName, inserted.middleName, inserted.lastName, inserted.ssN, inserted.userPersonId
INTO @temp (PersonId, firstName, middleName, lastName, ssN, userPersonId)
SELECT p.firstName, p.middleName, p.lastName, p.ssN, p.userPersonId 
FROM OPENJSON(@json)
  WITH (
    firstName varchar(100),
    middleName varchar(100),
    lastName varchar(100),
    ssN varchar(9),
    userPersonId int 
  ) as p;

SET @json = (
    SELECT *
    FROM @temp
    FOR JSON PATH
);

Or, if you have a unique natural key within the JSON (for example userPersonId), you can use some kind of join.

CREATE OR ALTER PROCEDURE [dbo].[SpPersonIns]
   @json nvarchar(max) OUTPUT
AS

DECLARE @temp TABLE (PersonId int not null, userPersonId int PRIMARY KEY);

INSERT INTO People (FirstName, MiddleName, LastName, SSN, UserPersonId)
OUTPUT inserted.PersonId, inserted.userPersonId
INTO @temp (PersonId, userPersonId)
SELECT p.firstName, p.middleName, p.lastName, p.ssN, p.userPersonId 
FROM OPENJSON(@json)
  WITH (
    firstName varchar(100),
    middleName varchar(100),
    lastName varchar(100),
    ssN varchar(9),
    userPersonId int 
  ) as p;

SET @json = (
    SELECT
      '[' +
      STRING_AGG(
        JSON_MODIFY(
          arr.value,
          '$.PersonId',
          t.PersonId
        ),
        ','
      ) +
      ']'
    FROM OPENJSON(@json) AS arr  --   if you don't specify a schema then you get the whole array object
    JOIN @temp t ON t.userPersonId = JSON_VALUE(arr.value, '$.userPersonId')
);

Stored procedure with multiple insert

1 Answers1