Stored procedure with multiple insert

SHARAD KAFLE 0
Viewed 37

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
open-json tsql sql-server sql json
1 Answers

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')
);
Charlieface0
Related