"Cursor" and "FOR XML" in Azure SQl Data Warehouse

Shubhi Jain 21 Reputation points
2021-05-14T13:51:58.26+00:00

while creating stored procedures in azure data warehouse I have got some error on "Cursor" and "FOR XML" so wanted to know are they supported by azure data warehouse or not. if not then what are the alternatives.
sample code with error msg pictures are attached herein.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,136 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Samara Soucy - MSFT 5,046 Reputation points
    2021-05-17T01:35:39.877+00:00

    Cursors can be replaced by while loops to get that functionality: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-loops

    In the case of FOR XML, the Synapse-compatible equivalent is going to be STRING_AGG(): https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=azure-sqldw-latest

    There are a couple things in the SQL language reference to help you find out whether a given command is available on your SQL version- first is by explicitly setting the docs to Synapse, and the second is to look at the supported versions in the header.

    96925-2021-05-16-21-29-30-string-agg-transact-sql-sql-se.png

    0 comments No comments