CODEWITHSUNDEEP
jsonsql-serverstored-procedures
ReaL_HyDRA
ReaL_HyDRA

Reputation: 355

Incorrect syntax while parsing JSON with OPENJSON

This is my SP, I am trying to parse this and the error

Incorrect syntax near '$.Role'

was shown.

The JSON is stored in a tables's column. What am I doing wrong?

 CREATE PROCEDURE [dbo].[sp_GetKeyPersonRoleMinMax]   
     @SectionID INT,
     @ProposalID INT
    AS  

    SET NOCOUNT ON

    Declare @FldKPRoleRequirementsList NVARCHAR(MAX)

    Declare @FldName varchar(50)        
    DEclare @FldIncl varchar(50)        
    Declare @FldRequired varchar(50)    
    Declare @FldLabel varchar(max)      
    Declare @FldList varchar(max)       

    CREATE Table #RoleMinMaxTemp
    ( 
    ID INT IDENTITY(1, 1) ,
    Role nvarchar(1000),
    MinRoleCount INT,
    MaxRoleCount INT
    )

    Declare Fld_Cursor Cursor For  
    SELECT FldName, FldIncl, FldRequired, FldLabel,FldList from tblFld where FldParent = 1367 AND FldName like 'FldKPRoleRequirementsList%'
    SET NOCOUNT ON          

    Open Fld_Cursor
    WHILE (@@Fetch_Status = 0)      
        BEGIN
            if (@FldName = 'FldKPRoleRequirementsList')
            BEGIN
                SET @FldKPRoleRequirementsList = @FldList
            END

        FETCH next from Fld_Cursor into  @FldName, @FldIncl, @FldRequired, @FldLabel,@FldList   
        END 

    Close Fld_Cursor        
    Deallocate Fld_Cursor

    IF(@FldKPRoleRequirementsList IS NOT NULL and Len(@FldKPRoleRequirementsList) >0)
       BEGIN
            INSERT INTO #RoleMinMaxTemp

            SELECT *  
            FROM OPENJSON(@FldKPRoleRequirementsList,'$.FldRole')  
              WITH (
                Role nvarchar(1000) '$.Role',
                MinRoleCount INT '$.MinRoleCount',
                MaxRoleCount INT '$.MaxRoleCount'
               );
       END;

What is the reason for this error? I am using SQL Server 2016.

Upvotes: 3

Views: 357

Answers (1)

ReaL_HyDRA
ReaL_HyDRA

Reputation: 355

Try changing you compactibility of SQL SERVER to 130. Your must be below that.

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130

Use this script to change it.

Upvotes: 4

Related Questions