I have a problem while using the below
ALTER PROCEDURE [SaveGlobalFines]
@JSON NVARCHAR(MAX)
AS
BEGIN
DECLARE @ChargeID INT
DECLARE @FineTypeID INT
DECLARE @AmountDB MONEY
DECLARE @AmountJSON MONEY
DECLARE @userID INT
DECLARE @Counter INT
SELECT @ChargeID=Fine.Charge_Id, @FineTypeID=Fine.FineType_Id, @AmountDB = Fine.Amount , @AmountJSON = JSON.Amount, @userID = JSON.user_id
FROM Fine
CROSS APPLY OPENJSON(@JSON,'$.amounts')
WITH(Charge_Id int, Amount money, FineType_Id int, user_id int)AS JSON
WHERE Fine.Charge_Id =JSON.Charge_Id and Fine.FineType_Id= JSON.FineType_Id and Fine.DateExpired IS NULL
IF @ChargeID IS NULL AND @FineTypeID IS NULL --if fine type and charge id are returned null insert new row
BEGIN
INSERT INTO Fine (Charge_Id,Amount,FineType_Id,user_id)
SELECT *
FROM OPENJSON(@JSON,'$.amounts')
WITH(Charge_Id int , Amount money, FineType_Id int, user_id int )AS JSON
END
ELSE-- Check if amount is the same if yes do not update current record and enter a new one
IF @AmountDB <> @AmountJSON
BEGIN
UPDATE Fine set DateExpired= GETDATE(), ExpiryDescription ='Updated by'+ CONVERT(NVARCHAR(10), JSON.user_id)
FROM OPENJSON(@JSON,'$.amounts')
WITH(Charge_Id int , Amount money, FineType_Id int, user_id int )AS JSON
WHERE Fine.Charge_Id =JSON.Charge_Id and Fine.FineType_Id=JSON.FineType_Id
INSERT INTO Fine (Charge_Id,Amount,FineType_Id,user_id)
SELECT *
FROM OPENJSON(@JSON,'$.amounts')
WITH(Charge_Id int, Amount money, FineType_Id int, user_id int)AS JSON
END
END
Abviously the vairables example @chargeID is being overwritten by the last element in the json file. This works
SELECT *
FROM OPENJSON(@JSON,'$.amounts[0]')
WITH(Charge_Id int, Amount money, FineType_Id int, user_id int)AS JSON
I wish to do something like the below but with a counter instead since the JSON file is not always the same. It might have only one element and teh above will work correctly but if it has 4 elements it does not work
SELECT *
FROM OPENJSON(@JSON,'$.amounts[+'@Counter'+]')
WITH(Charge_Id int, Amount money, FineType_Id int, user_id int)AS JSON
But this fire an error. JSON being passed is like the below
> "amounts": [
> {
> "Charge_Id": "368",
> "Amount": "800",
> "FineType_Id": 3,
> "user_id": 2
> },
> {
> "Charge_Id": "368",
> "Amount": "600",
> "FineType_Id": 4,
> "user_id": 2
> } ]
Please help
@counter
with the number of records in theJSON
or only return specific records from theJSON
?