3

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
F3LIX BLANC0
  • 63
  • 2
  • 10
  • Actually, OPENJSON was introduced in SQL 2016. But have a look here: https://stackoverflow.com/questions/2867501/parse-json-in-tsql – squillman Nov 07 '18 at 19:34
  • You could convert json to xml and then use XML, this would be an alternative approach. (google json to xml sql function) – iSR5 Nov 07 '18 at 19:50
  • Thanks for the correction. And I've read through all the comments and answers in that link, none seem to be helpful unfortunately. @squillman – F3LIX BLANC0 Nov 07 '18 at 19:54
  • 1
    What exactly are you looking for? Phil Factor's answer gives you an alternative. There is no direct alternative to OPENJSON in SQL 2014. It simply doesn't support parsing JSON natively. – squillman Nov 07 '18 at 20:03
  • A possible function that could. I apologize I didn't mean to come off as rude. In that link, it was just a lot of help towards SQL 2016 and not 2014. Perhaps I'm not looking in the right places. @squillman – F3LIX BLANC0 Nov 07 '18 at 20:05
  • @iSR5 thank you for the suggestion. I'm currently dabbling with it now. – F3LIX BLANC0 Nov 07 '18 at 20:06
  • No worries, wasn't trying to imply you were sounding rude! Here's the directly link to Phil Factor's function. It's a bit of a read... https://www.red-gate.com/simple-talk/sql/t-sql-programming/consuming-json-strings-in-sql-server/ – squillman Nov 07 '18 at 20:11

0 Answers0