3

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.

1 Answer 1

4

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.