Your stored procedure Custom_Procedure is designed to parse JSON input
containing product information and insert that data into the Product table.
Here's a breakdown of what it does
Parsing JSON for Product: The OPENJSON function parses the JSON input @ProductJson
and maps its elements to columns of a temporary table #ProductTable. The WITH clause specifies
the mapping of JSON keys to table columns
CREATE PROCEDURE [dbo].[Custom_Procedure]
@ProductJson nvarchar(max) = NULL
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
DROP TABLE IF EXISTS #ProductTable;
-- Parsing JSON
for Product
SELECT *
INTO #ProductTable
FROM OPENJSON(@ProductJson)
WITH
(
Id int '$.Id',
Name nvarchar(255) '$.Name',
Price decimal(18, 2) '$.Price'
);
-- Insert data
into Product table
INSERT INTO Product
(Id, Name, Price)
SELECT Id, Name, Price
FROM #ProductTable;
-- Commit
transaction if successful
COMMIT TRANSACTION;
-- Return
success message
SELECT 'Successful record Insert!' AS ErrorMessage, 1 AS ResultType;
END TRY
BEGIN CATCH
-- Rollback
transaction if an error occurs
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END;
-- Return error
message
SELECT 'An error occurred. Please try again.' AS ErrorMessage, 0 AS ResultType;
END CATCH;
END;
CREATE PROCEDURE [dbo].[Custom_Procedure]
@ProductJson nvarchar(max) = NULL
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
DROP TABLE IF EXISTS #Table1;
DROP TABLE IF EXISTS #Table2;
DECLARE
@NewId uniqueidentifier = NULL, -- Updated parameter name
@NewCreatedDate datetime = NULL, -- Updated parameter name
@NewUpdatedDate datetime = NULL, -- Updated parameter name
@NewTableId uniqueidentifier = NULL; -- Updated parameter name
-- Parsing JSON
for Table1
SELECT *
INTO #Table1
FROM OPENJSON(@ProductJson , '$.Table1Model')
WITH
(
NewPKId uniqueidentifier '$.NewPKId', -- Updated parameter name name
NewColumn1 datatype '$.NewColumn1', -- Updated parameter name and datatype
NewColumn2 datatype '$.NewColumn2', -- Updated parameter name and datatype
NewIsDeleted bit '$.NewIsDeleted' -- Updated parameter name
);
-- Insert data
into Table1
INSERT INTO Table1
(PKId,
Column1,
Column2,
...,
IsDeleted)
SELECT NewPKId, -- Updated parameter name
NewColumn1, -- Updated
parameter name
NewColumn2, -- Updated
parameter name
...,
NewIsDeleted -- Updated parameter name
FROM #Table1;
-- Parsing JSON
for Table2
SELECT *
INTO #Table2
FROM OPENJSON(@ProductJson , '$.Table2Model')
WITH
(
NewPKId uniqueidentifier '$.NewPKId', -- Updated parameter name
NewColumnA datatype '$.NewColumnA', -- Updated parameter name and datatype
NewColumnB datatype '$.NewColumnB', -- Updated parameter name and datatype
NewIsDeleted bit '$.NewIsDeleted' -- Updated parameter name
);
-- Insert data
into Table2
INSERT INTO Table2
(PKId,
ColumnA,
ColumnB,
...,
IsDeleted)
SELECT NewPKId, -- Updated parameter name
NewColumnA, -- Updated
parameter name
NewColumnB, -- Updated
parameter name
...,
NewIsDeleted -- Updated parameter name
FROM #Table2;
-- Commit
transaction if successful
COMMIT TRANSACTION;
-- Return
success message
SELECT 'Successful record Insert!' AS ErrorMessage, 1 AS ResultType;
END TRY
BEGIN CATCH
-- Rollback
transaction if an error occurs
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END;
-- Return error
message
SELECT 'An error occurred. Please try again.' AS ErrorMessage, 0 AS ResultType;
END CATCH;
END;