Azure Synapse Analytics Cursor Alternative

SQL Cursor is a database object to retrieve data from a result set one row at a time. Database such as Oracle, Teradata, Microsoft SQL Server support cursors. The cursor are useful in many scenarios such as retrieving records in a loop. SQL Cursor always returns one row at a time, you can perform your calculation on returned values. In this article, we will check what is Azure Synapse Analytics Cursor Alternative with an example.

Does Azure Synapse Analytics support SQL Cursor Variable?

The Microsoft SQL Server, which is based on T-SQL support cursors. However, Azure Synapse Dedicated SQL pool which is also based on T-SQL does not support cursor variables.

Following example on a SQL Server uses a cursor variable to table names and while loop to loop through cursor records.

DECLARE @name VARCHAR(50) -- Table name 

DECLARE db_cursor CURSOR FOR 
SELECT [name] FROM sys.tables

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
PRINT @name
FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

We will refer this sample cursor for subsequent alternative approach.

What is SQL Cursor Alternative in Azure Synapse Analytics?

You can think SQL cursor as a temporary workspace provisioned in database system memory when a SQL query is executed. So one of the easiest way is to create a temporary table and iterate through rows in temp table using looping structures.

Note that, temp table is just an alternative. In reality, SQL cursor variable updates the pointer whenever data is read. But, in the temporary table, you cannot implement the pointer concept.

Following is the example of using a temporary table as a SQL cursor alternative.

-- Create Temp table instead of cursor
IF OBJECT_ID(N'tempdb..#tmpTables') IS NOT NULL
BEGIN
DROP TABLE #tmpTables
END
GO
create table #tmpTables
with (
DISTRIBUTION = ROUND_ROBIN
)
AS SELECT [name] FROM sys.tables;

DECLARE @name VARCHAR(50) -- database name 
DECLARE @counter int = 1
DECLARE @recordCount int = (SELECT COUNT(1) from #tmpTables)

-- Loop through records in Temp table
WHILE @counter <= @recordCount 
BEGIN  
	  SET @name = (SELECT [name]
	  FROM(SELECT *, ROW_NUMBER() OVER(ORDER BY [name]) AS RN
	  FROM #tmpTables) as T
	  WHERE RN = @counter)

	  PRINT @name

	  SET @counter = @counter + 1
END

As you can see in the above example, it is easy to loop through temporary table.

However, following are some of points to be noted.

We are using the SET command to assign value to a variable.
We are not using open and close cursor.
The TEMPORARY table is created in place of cursor declaration.