question

ShubhiJain-5090 avatar image
0 Votes"
ShubhiJain-5090 asked SamaraSoucy-MSFT answered

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

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.


[1]: /answers/storage/attachments/96767-for-xml.jpg

[2]: /answers/storage/attachments/96768-cursor.jpg

azure-synapse-analytics
for-xml.jpg (30.5 KiB)
cursor.jpg (35.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SamaraSoucy-MSFT avatar image
0 Votes"
SamaraSoucy-MSFT answered

Cursors can be replaced by while loops to get that functionality: https://docs.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://docs.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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.