Reputation: 355
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
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