Formatted JSON Statement Causes Error

idprdigital19

I am getting some errors when I format code using SQL Prompt 7.3.0.775 on SQL 2016 using the JSON FOR syntax. Here is some example code:

This code works:

DROP TABLE IF EXISTS #Test
CREATE TABLE #Test
(
    Id INT,
	FileLoadStatsId INT
)

INSERT INTO #Test
        ( Id, FileLoadStatsId )
VALUES  ( 0, -- Id - int
          0  -- FileLoadStatsId - int
          )

INSERT INTO #Test
        ( IdInsertRecordJSON NVARCHAR(MAX) = 
(SELECT id, ISNULL(FileLoadStatsId, -1) AS FileLoadStatsId FROM  #Test FOR JSON AUTO);
-- SQL Prompt formatting on

 SELECT id,
    IIF(FileLoadStatsId = -1, NULL, FileLoadStatsId) AS FileLoadStatsId
FROM
    OPENJSON(@InsertRecordJSON)
    WITH
    (
        id INT 'strict $.id',
        FileLoadStatsId INT 'strict $.FileLoadStatsId'
    );

However, if I format it using SQL Prompt to the following, it does not work as SSMS will throw an error of " Property cannot be found on the specified JSON path." The only thing that is changing between the two code blocks is where I am setting the value of the @InsertRecordJSON variable. Here is the code that doesn't work :

DROP TABLE IF EXISTS #Test
CREATE TABLE #Test
(
    Id INT,
	FileLoadStatsId INT
)

INSERT INTO #Test
        ( Id, FileLoadStatsId )
VALUES  ( 0, -- Id - int
          0  -- FileLoadStatsId - int
          )

INSERT INTO #Test
        ( IdInsertRecordJSON NVARCHAR(MAX) = (
                                              SELECT Id,
                                                  ISNULL(FileLoadStatsId, -1) AS FileLoadStatsId
                                              FROM  #Test
                                              FOR JSON AUTO
                                          );


 SELECT id,
    IIF(FileLoadStatsId = -1, NULL, FileLoadStatsId) AS FileLoadStatsId
FROM
    OPENJSON(@InsertRecordJSON)
    WITH
    (
        id INT 'strict $.id',
        FileLoadStatsId INT 'strict $.FileLoadStatsId'
    );

I have tried taking the formatted code and removing all the CRLFs and the spacing and it still fails. Any thoughts on why the formatted code causes it to throw errors? If I take the formatted code and remove the formatting manually, it still won't work. So it seems like something is happening during the format, that I can't see, that is causing this to fail.

Thanks in advance!
________________

PR Digital