Incorrect syntax near the keyword 'with' in stored procedure when using OPENJSON

Question

Hi I created a stored procedure that uses OPEN JSON and insert data into a table.

The problem is when I run the stored procedure it shows an error.

I am using SQL server 2016 (SQl Server 13.0.4446.0). I am not getting the same issue when using using sql server 13.0.1742.0

CREATE PROCEDURE [dbo].Test2--'[{"FileId":1,"DataRow":"3000926900"}]'
(    
@data  varchar(max)       
)
AS
BEGIN
create table #Temp
(
   FileId bigint,
   DataRow nvarchar(max),
   DateLoaded DateTime
)    
  INSERT INTO [dbo].#Temp
    SELECT * FROM OPENJSON(@data)
    WITH (FileId bigint,
      DataRow nvarchar(max),
      DateLoaded DateTime)    
      select * from #temp
END

Error: If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Answer 1

Check your database compatibility level. OPENJSON is new with SQL Server 2016, and if your compatibility level is set "SQL Server 2014 (120)" or less the OPENJSON function will not be correctly recognized or executed. See the MSDN docs at https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql .