How to iterate over query result set in Azure Synapse Serverless SQL Pool stored procured? The procedure works on SQL dedicated

Question

I am migrating stored procedure from SQL Dedicated to Synapse Serverless SQL Pool.

The procedure queries a table and stores the results into a temporary table.

Then it iterates over the temporary table rows.

I am able to create the procedure with no issues, but when I run, I got the following error:

Msg 15816, Level 16, State 2, Procedure test.test_temp_table, Line 7
The query references an object that is not supported in distributed processing mode.

What I realized is Serverless SQL does not allow temporary tables. What are the alternatives to iterate over query results inside a procedure for Serverless SQL?

I tried all these options:

CREATE TABLE #temp_table
WITH
( DISTRIBUTION = ROUND_ROBIN)
AS
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Sequence,
FIELD1, FIELD2, FIELD3...
FROM OPENROWSET(
    BULK '<delta-table path>',
    FORMAT = 'DELTA',
    DATA_SOURCE = '<ds-name>'
) AS source

CREATE TABLE #temp_table
WITH
( DISTRIBUTION = ROUND_ROBIN)
AS
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Sequence,
FIELD1, FIELD2, FIELD3...
FROM [<schema>].[<view_name>]

CREATE TABLE #temp_table
WITH
( DISTRIBUTION = ROUND_ROBIN)
AS
SELECT FIELD1, FIELD2, FIELD3...
FROM [<schema>].[<view_name>]

CREATE TABLE #temp_table (
    FIELD1 VARCHAR(1020),
    FIELD2 VARCHAR(80)
)

INSERT INTO #temp_table
SELECT FIELD1, FIELD2
FROM OPENROWSET(
        BULK '<delta-table path>',
        FORMAT = 'DELTA',
        DATA_SOURCE = '<ds-name>'
) AS source

Answer 1

When you try to insert data into temp table using below query:

create table #temp([variety] nvarchar(4000),
    [TotalsepalWidth] float)


INSERT INTO #temp
 SELECT [variety],
            SUM([sepal.width]) AS TotalsepalWidth
FROM OPENROWSET(
    BULK 'Iris.parquet',
    DATA_SOURCE = 'container1_badlss_dfs_core_windows_net',
    FORMAT = 'PARQUET'
) AS source 
GROUP BY [variety]  

You will get above error:

According to the MS document

The above error indicates that

you've used an object or function that can't be used while you query data in Azure Storage or Azure Cosmos DB analytical storage.

To resolve above error, you can follow below procedure:

Create a sample view for the data rather than repeating the OPENROWSET syntax for each select. Wrap the SELECT statement in a stored procedure and insert into a temp table. Create stored procedure as below:

CREATE PROC dbo.aggirisprocedure
AS
BEGIN
     SELECT [variety],
     SUM([sepal.width]) AS TotalsepalWidth
    FROM dbo.vw_iris
    GROUP BY [variety] 
END;

Create temp table and execute stored procedure to insert values as below query:

create table #temp([variety] nvarchar(4000),
    [TotalsepalWidth] float)

INSERT INTO #temp EXEC dbo.aggirisprocedure
SELECT * FROM #temp;

The data will insert into table successfully as shown below:

For more information you can refer to this.