How to use OPENJSON or a compatible equivalent in SQL Server 2014?

Viewed 6322

I am aware OPENJSON is a new feature in SQL Server 2016.

I currently have the stored procedure shown below working for SQL Server 2017, but I cannot figure out a way to make it work on SQL Server 2014 or find a compatible equivalent.

The current error I get is as follows:

Incorrect syntax near the keyword 'WITH'.

Incorrect syntax near the keyword 'with'. 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.

My code:

CREATE PROCEDURE ProcedureName 
    @submissionID INT
AS
    DECLARE @submission_FormDefn AS INT
    DECLARE @Defn_json AS NVARCHAR(MAX)
    DECLARE @Subm_json AS NVARCHAR(MAX) 

    SELECT  
        @Subm_json = [jsonPost],  
        @submission_FormDefn = [FormDefn_Id] 
    FROM 
        form_submissions 
    WHERE
        id = @submissionID

     SET @Defn_json = (SELECT [data] 
                       FROM form_defns 
                       WHERE id = @submission_FormDefn)

     SELECT 
         dd.name, dd.label, dd.type, V.VALUE 
     FROM
         [dbo].[form_submissions_VALUES] V
     INNER JOIN 
         OPENJSON(@Defn_json) WITH (name varchar(MAX) '$.name',
                                    label varchar(MAX) '$.label',
                                    type varchar(MAX) '$.type') dd ON V.field = dd.name
     WHERE  
         type IN ('one', 'two', 'three') 
         AND v.FormSubmission_Id = @submissionID
GO
0 Answers
Related