SQL Server: BLANK JSON entry in JSON causes error

Please share error message

Add NULL while creating Table to allow blank and NULL

CREATE TABLE #Temp (ID INT, Prd VARCHAR(4), CreditLimit DECIMAL(18, 2)  NULL, AccHolder VARCHAR(15) NULL, PrdNumber VARCHAR(12) NULL)

I tried but getting following error -> Error converting data type nvarchar to decimal.

Hi tsakumar81,

Define Column as VARCHAR DataType and check when empty then, set with zero using the CASE statement in select query.

Refer below query.

SET NOCOUNT ON;
DECLARE @json NVARCHAR(MAX) = N'[
{ "ID":"1", "Product":"A", "ProductCreditLimit":"", "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"}]';

IF OBJECT_ID('tempdb..#Temp', 'U') IS NOT NULL
BEGIN
	DROP TABLE #Temp
END

CREATE TABLE #Temp (ID INT, Prd VARCHAR(4), CreditLimit DECIMAL(18, 2), AccHolder VARCHAR(15), PrdNumber VARCHAR(12))
INSERT INTO #Temp
SELECT	ID
		,Product
		,CASE WHEN ProductCreditLimit = '' THEN '0' ELSE ProductCreditLimit END -- Check when empty then set zero.
		,AccountHolder
		,ProductNumber
FROM OPENJSON(@json)
WITH 
(	
	ID INT,
	Product VARCHAR(12),
	ProductCreditLimit VARCHAR(100), -- Define VARCHAR DataType.
	AccountHolder VARCHAR(15),
	ProductNumber NVARCHAR(15)
)

SELECT * FROM #temp;