none
Incorrect syntax near 'FOR' when running query on Azure SQL DB RRS feed

  • Question

  • Hi, I'm trying to run the following query on my database for grouped concatenation:

    CREATE PROCEDURE sp_CommaSeparatedLocations
    @SequenceSepatator NVARCHAR(5)
    
    AS
    
    SET NOCOUNT ON;
    SELECT
          [CustomerID]
        , [LocationSequence] = STUFF((
              SELECT @SequenceSepatator + ma.Location
              FROM Customer ma
              WHERE m.CustomerID = ma.CustomerID
    		  ORDER BY [Timestamp]
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(4000)'), 1, 1, '')
    INTO CaseSequences
    FROM Customer m
    GROUP BY [CustomerID]
    ORDER BY [CustomerID];
    GO

    Original Data:

    1 NYC 2013-07-02 14:18:41.0000000

    1 DXB  2013-07-09 12:19:16.0000000

    Expected Result after running Stored Procedure:

    1 NYC,DXB

    In short, I'm trying to comma separate a user's visited locations sorted according to the timestamp. Is FOR XML PATH not allowed on Azure SQL DB?

    Thank you!



    • Edited by syedaliammar Monday, November 16, 2015 4:53 PM Better subject
    Monday, November 16, 2015 4:45 PM

All replies

  • Hi AmmarRizvi,

    I just tried your proc on a V12 server and it works fine. Can you share the exact error message that you see? What might be happening is that you get an error on subsequent executions of the proc because the table CaseSequences already exists.

    Best,

    Jan

    Monday, December 7, 2015 6:17 PM