Processing data row by row while bulk insert using JSON in SQl server

Need to Generate Tag Number While Inserting Data using JSON in SQl Server Stored Procedure.

I have 2 Tables Table1: Category (lastusedIndex null in initial stage)

Table 2: asset (Inserting data into this table)

It has following column

Id, AssetTag, SerialNumber, categoryid

I am Inserting multiple records at same time, While Inserting record the AssetTag need to be generated automatically with asset prefix

after generating the asset tag for a item we need to increment lastUsedIndex of particular category in Category Table(Here keyboard) or we need to increment lastUsedIndex and concatenate with assetPrefix

For Example: For key Board AssetTag would be KYB00001, KYB00002.....

Example Json Data

declare @Asset_Info_Json nvarchar(max)
set @Asset_Info_Json = N'[{
"SerialNumber" : "S1",
"categoryid" : "1"
},{
"SerialNumber" : "S2",
"categoryid" : "1"
}]'
exec USP_TEST @Asset_Info_Json

Expected output (Id auto generated)

Here is my stored procedure

Create procedure USP_TEST(@Asset_Info_Json varchar(max))
as
 begin
 begin transaction save_asset
 begin try


Insert into asset(AssetTag,SerialNumber,categoryid)
select  
 AssetTag = (select assetPrefix + cast(FORMAT(isnull(lastusedIndex,0) + 1,'0000#') as varchar) from category where id = CategoryId),
 SerialNumber,categoryid
From OPENJSON(@Asset_Info_Json)
WITH (SerialNumber varchar(30),categoryid varchar(30))



commit transaction save_asset
 end try
 begin catch
 rollback transaction save_asset
 end catch
end

Query for generate tag and update index column

update category set lastusedIndex = isnull(lastusedIndex,0) + 1 
OUTPUT Inserted.assetPrefix + cast(FORMAT(Inserted.lastusedIndex, '0000#') as varchar) as SerialNumber
where id = 1 

I tried table variable but it works for single item

We cannot use custom function to generate Asset Tag because We cannot use Update statement inside function

Multiple AssetTag values can be calculated using ROW_NUMBER.

You need to OUTPUT the AssetTag and categoryid into a table variable, then aggregate it back up to get the last id, in order to update the category table.

Because the AssetTag is a varchar column, it would be complex to have to parse the number back out. Instead you can use MERGE in order to OUTPUT columns which are not being inserted.

• You should also use locking hints on category to prevent concurrency issues.
• Do not messa round with CATCH... ROLLBACK instead just use SET XACT_ABORT ON which does it all for you.
• If the number of categories is small, consider changing this whole design to use SEQUENCE instead.

CREATE OR ALTER PROC USP_TEST
  @Asset_Info_Json nvarchar(max)
AS

SET XACT_ABORT, NOCOUNT ON;

BEGIN TRAN;

DECLARE @indexes TABLE (categoryid int, NewIndex int, PRIMARY KEY (categoryid, NewIndex));

WITH source AS (
    SELECT
      j.SerialNumber,
      j.categoryid,
      c.assetPrefix,
      NewIndex = ISNULL(c.lastusedIndex, 0) + ROW_NUMBER() OVER (PARTITION BY j.categoryid ORDER BY j.SerialNumber)
    FROM OPENJSON(@Asset_Info_Json)
      WITH (
        SerialNumber varchar(30),
        categoryid varchar(30)
      ) j
    JOIN category c WITH (HOLDLOCK, UPDLOCK) ON c.id = j.categoryId
)
MERGE asset a
USING source s
ON 1=0 --never match
WHEN NOT MATCHED THEN
  INSERT
    (AssetTag, SerialNumber, categoryid)
  VALUES
    (s.assetPrefix + FORMAT(s.NewIndex, '0000#'), s.SerialNumber, s.categoryid)
OUTPUT s.categoryid, s.NewIndex
INTO @indexes (categoryid, NewIndex)
;

UPDATE c
SET lastusedIndex = LastIndex
FROM category c
JOIN (
    SELECT
      i.categoryid,
      LastIndex = MAX(i.NewIndex)
    FROM @indexes i
    GROUP BY i.categoryid
) i ON i.categoryid = c.id;

COMMIT TRAN;