I need to validate json entries passed to sql server, please advise
DECLARE @json NVARCHAR(MAX) = N'[{ "ID":"1", "Product":"A", "ProductCreditLimit":"12357.00", "AccountHolder":"29150820", "ProductNumber":"00098683"},
{ "ID":"2", "Product":"B", "ProductCreditLimit":"3434.00", "AccountHolder":"73247202", "ProductNumber":"00098706"},
{ "ID":"3", "Product":"C", "ProductCreditLimit":"1000.00", "AccountHolder":"73212071", "ProductNumber":"00098684"},
{ "ID":"4", "Product":"D", "ProductCreditLimit":"34000.00", "AccountHolder":"73247205", "ProductNumber":"343434444"}]}';
declare @prdcount int
SELECT @prdcount = COUNT(*) FROM OPENJSON(@json)
DECLARE @MAXID INT, @Counter INT,@PrdNumber varchar(12), @CreditLimit decimal(18, 2),@Prd varchar(4),
@AccHolder varchar(15)
DECLARE @RowCount INT = @prdcount;
WHILE @RowCount > 0
BEGIN
select
ID,Product,ProductCreditLimit,AccountHolder,ProductNumber
FROM OPENJSON(@json)
WITH (ID int,
Product varchar(4),
ProductCreditLimit decimal(18,2),
AccountHolder varchar(15),
ProductNumber varchar(12))
SET @RowCount-=1
END
