SQL Data Tools Visual Studio Syntax error but not in Management Studio

Question

I have built the following query: (snippet)

SELECT *
FROM OPENJSON (@JSON, '$.records[' + CAST(@arrayNr AS VARCHAR(10)) + ']')

Variable @arrayNr is used within a cursor so every loop it has a different value to loop through all records arrays in my json.

Now, this query works excellent, and gives no errors in Management Studio.

When I paste the query into Visual Studio to add the stored procedure to source control, it gives me the following error:

SQL46010: Incorrect syntax near +

Which is the first + before the CAST function.

I am using Azure SQL Database (SQL Server 2017) and the Visual Studio settings are also set to that (tried others with no success).

Have I found a bug in SSDT? I have set the build action to none and it will remove the error, but I really want to build the stored procedure. Any tips?

Answer 1

Incase anyone else is having this error... I made a workaround by putting the entire query within a variable and using EXEC(@variable) to execute the query.

Looks like this:

SET @sqlCMD = 'SELECT *
     FROM OPENJSON ('''+@JSON+''', ''$.records[' + CONVERT(VARCHAR(10),@arrayNr) + ']'')'