Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    63
    Provided Answers: 1

    Question Unanswered: SQL Server stored procedure to JSON error

    I am trying to write the following stored procedure:
    Code:
        SET ANSI_NULLS ON
        GO
        SET QUOTED_IDENTIFIER ON
        GO
        
        ALTER PROCEDURE [dbo].[spInsertAudit]
            (@val1 VARCHAR(100),
         	 @val2 VARCHAR(200))
        AS
        BEGIN TRY
        	DECLARE	@return_value varchar(max)
        	/************************************/
        	/*            The Query             */
        	/************************************/
        	EXEC [dbo].[SerializeJSON] 
        	'INSERT INTO tLogin (EMP, LoginInTime) 
             VALUES (@val1, @val2)'
        END TRY
        BEGIN CATCH
        	/************************************/
        	/*      Get Error results back      */
        	/************************************/
        	EXEC [dbo].[SerializeJSON] 
        	'SELECT ERROR_NUMBER()	AS ErrorNumber,
        		ERROR_SEVERITY()	AS ErrorSeverity,
        		ERROR_STATE()		AS ErrorState,
        		ERROR_PROCEDURE()	AS ErrorProcedure,
        		ERROR_LINE()		AS ErrorLine,
        		ERROR_MESSAGE()		AS ErrorMessage'
        END CATCH
    The `SerializeJSON` stored procedure (found here) is this:
    Code:
        SET ANSI_NULLS ON
        GO
        SET QUOTED_IDENTIFIER ON
        GO
         
        ALTER PROCEDURE [dbo].[SerializeJSON] 
            (@ParameterSQL AS VARCHAR(MAX))
        AS
        BEGIN
            DECLARE @SQL NVARCHAR(MAX)
            DECLARE @XMLString VARCHAR(MAX)
            DECLARE @XML XML
            DECLARE @Paramlist NVARCHAR(1000)
         
            SET @Paramlist = N'@XML XML OUTPUT'
            SET @SQL = 'WITH PrepareTable (XMLString)'
            SET @SQL = @SQL + 'AS('
            SET @SQL = @SQL + @ParameterSQL + ' FOR XML RAW,TYPE,ELEMENTS'
            SET @SQL = @SQL + ')'
            SET @SQL = @SQL + 'SELECT @XML=[XMLString]FROM[PrepareTable]'
         
            EXEC sp_executesql @SQL
                , @Paramlist
                , @XML = @XML OUTPUT
         
            SET @XMLString = CAST(@XML AS VARCHAR(MAX))
         
            DECLARE @JSON VARCHAR(MAX)
            DECLARE @Row VARCHAR(MAX)
            DECLARE @RowStart INT
            DECLARE @RowEnd INT
            DECLARE @FieldStart INT
            DECLARE @FieldEnd INT
            DECLARE @KEY VARCHAR(MAX)
            DECLARE @Value VARCHAR(MAX)
            DECLARE @StartRoot VARCHAR(100);
         
            SET @StartRoot = '<row>'
         
            DECLARE @EndRoot VARCHAR(100);
         
            SET @EndRoot = '</row>'
         
            DECLARE @StartField VARCHAR(100);
         
            SET @StartField = '<'
         
            DECLARE @EndField VARCHAR(100);
         
            SET @EndField = '>'
            SET @RowStart = CharIndex(@StartRoot, @XMLString, 0)
            SET @JSON = ''
         
            WHILE @RowStart > 0
            BEGIN
                SET @RowStart = @RowStart + Len(@StartRoot)
                SET @RowEnd = CharIndex(@EndRoot, @XMLString, @RowStart)
                SET @Row = SubString(@XMLString, @RowStart, @RowEnd - @RowStart)
                SET @JSON = @JSON + '{'
                -- for each row
                SET @FieldStart = CharIndex(@StartField, @Row, 0)
         
                WHILE @FieldStart > 0
                BEGIN
                    -- parse node key
                    SET @FieldStart = @FieldStart + Len(@StartField)
                    SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
                    SET @KEY = SubString(@Row, @FieldStart, @FieldEnd - @FieldStart)
                    SET @JSON = @JSON + '"' + @KEY + '":'
                    -- parse node value
                    SET @FieldStart = @FieldEnd + 1
                    SET @FieldEnd = CharIndex('</', @Row, @FieldStart)
                    SET @Value = SubString(@Row, @FieldStart, @FieldEnd - @FieldStart)
                    SET @JSON = @JSON + '"' + @Value + '",'
                    SET @FieldStart = @FieldStart + Len(@StartField)
                    SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
                    SET @FieldStart = CharIndex(@StartField, @Row, @FieldEnd)
                END
         
                IF LEN(@JSON) > 0
                    SET @JSON = SubString(@JSON, 0, LEN(@JSON))
                SET @JSON = @JSON + '},'
                --/ for each row
                SET @RowStart = CharIndex(@StartRoot, @XMLString, @RowEnd)
            END
         
            IF LEN(@JSON) > 0
                SET @JSON = SubString(@JSON, 0, LEN(@JSON))
            SET @JSON = '[' + @JSON + ']'
         
            SELECT @JSON
        END
    When I execute the `spInsertAudit` stored procedure, I get this JSON error:
    Code:
    [
      {
        "ErrorNumber": "156",
        "ErrorSeverity": "15",
        "ErrorState": "1",
        "ErrorLine": "1",
        "ErrorMessage": "Incorrect syntax near the keyword 'INSERT'."
      }
    ]
    So im not sure why its saying that since it looks correct to me?
    Last edited by StealthRT; 04-26-17 at 16:03.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,858
    Provided Answers: 17
    The usual way of troubleshooting Dynamic SQL is to toss in a PRINT @SQL statement just before the call to sp_executesql.

    I have not worked with the XML OUTPUT features in SQL 2016, very much, but I expect you need to feed that a SELECT query, rather than an INSERT statement.

  3. #3
    Join Date
    Jul 2003
    Posts
    63
    Provided Answers: 1
    Quote Originally Posted by MCrowley View Post
    The usual way of troubleshooting Dynamic SQL is to toss in a PRINT @SQL statement just before the call to sp_executesql.

    I have not worked with the XML OUTPUT features in SQL 2016, very much, but I expect you need to feed that a SELECT query, rather than an INSERT statement.
    You seem to be correct as I am getting this:
    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'INSERT'.
    Msg 156, Level 15, State 1, Line 6
    Incorrect syntax near the keyword 'FOR'.
    And the SQL it makes looks like this:
    Code:
    WITH PrepareTable (XMLString)AS(INSERT INTO 
    		tLogin (EMP, LoginInTime) 
        VALUES 
    		('something', '2017-04-25 15:59:48.000') FOR XML RAW,TYPE,ELEMENTS)SELECT @XML=[XMLString]FROM[PrepareTable]
    But I am not sure how to go about fixing what its asking?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,858
    Provided Answers: 17
    The WITH keyword starts a Common Table Expression (usually CTE). A CTE can't take an INSERT statement. Is the goal to return an XML blob with the 'something' and the datetime value? In that case, you might need something more like:
    Code:
    with PrepareTable (XMLString)
    as
    (select 'something' as EMP, getdate() as LoginInTime
     FOR XML RAW,TYPE,ELEMENTS)
    
    select *
    from PrepareTable
    Once the insides of the dynamic SQL call are settled, you should be all set. If you need to have the INSERT statement run, you could also play with the OUTPUT clause to recover the values. Something like

    Code:
    declare @logintab table (EMP varchar(100), LoginInTime datetime)
    declare @XML xml
    
    insert into tLogin(EMP, LoginInTime)
    output inserted.EMP, inserted.LoginInTime into @logintab (EMP, LoginInTime)
    values ('something', getdate())
    
    select @XML = (select EMP, LoginInTime from @logintab FOR XML RAW,TYPE,ELEMENTS)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •