Create a Cursor using Dynamic SQL Query

Even though we are advised not to use or to minimize the usage of cursors in SQL, there are times which, it’s the only available option, depending on the task and the requirement.

For example, there can be a situation where we need to execute a stored process, using a value of one or more columns in another table or tables, as input parameters, and update the source tables using it's return value. So in a similar situation you need to use a cursor. And there are times it's required to build the cursor using a dynamic SQL Select statement. Especially when the source table is not static, or it is depending on a value on a different table etc...

To illustrate this, I will create two tables and populated it with sample data.

--==== Create sample tables ====--
create table sample_table_a(
    id        int
    ,name    varchar(50)
)
 
create table sample_table_b(
    id            int
    ,country    varchar(50)
)
 
--==== Populate with sample data ====--
insert into sample_table_a (id,name)
values(1,'Windows'),(2,'Mac OS'),(3,'Linux')
 
insert into sample_table_B(id,country)
values(1,'Austria'),(2,'Australia'),(3,'USA'),(4,'Singapore')  

When creating a cursor using dynamic query, initially the cursor should be declared and passed on to the dynamic query execution.

/* ==== Variable Declaration ==== */
declare @objcursor as cursor 
 
declare 
    @vsql        as nvarchar(max)
    ,@vquery    as nvarchar(max)
    ,@id        as int
    ,@value        as varchar(50)
    
 
/* ==== Sample Table A ==== */
set @vquery = 'select id, name from sample_table_a'
set @vsql = 'set @cursor = cursor forward_only static for ' + @vquery + ' open @cursor;'
 
exec sys.sp_executesql
    @vsql
    ,N'@cursor cursor output'
    ,@objcursor output
 
fetch next from @objcursor into @id,@value
while (@@fetch_status = 0)
begin
    print cast(@id as varchar) + ' - ' + @value
    fetch next from @objcursor into @id,@value
end
 
close @objcursor
deallocate @objcursor
 
/* ==== Sample Table B ==== */
 
set @vquery = 'select id, country from sample_table_b'
set @vsql = 'set @cursor = cursor forward_only static for ' + @vquery + ' open @cursor;'
 
exec sys.sp_executesql
    @vsql
    ,N'@cursor cursor output'
    ,@objcursor output
 
fetch next from @objcursor into @id,@value
while (@@fetch_status = 0)
begin
    print cast(@id as varchar) + ' - ' + @value
    fetch next from @objcursor into @id,@value
end
 
close @objcursor
deallocate @objcursor

In the above example, I have used a dynamic select query to create a trigger. And you can see, the source table can be either 'sample_table_a' or 'sample_table_b'. This can differ based on another condition or a variable value.

--==== Conditional ====--
if(@condition = 'A')
begin
	set @vquery = 'select id, name from sample_table_a'
end
else
begin
	set @vquery = 'select id, name from sample_table_b'
end
set @vsql = 'set @cursor = cursor forward_only static for ' + @vquery + ' open @cursor;'
exec sys.sp_executesql
	@vsql
	,N'@cursor cursor output'
	,@objcursor output

fetch next from @objcursor into @id,@value
while (@@fetch_status = 0)
begin
	print cast(@id as varchar) + ' - ' + @value
	fetch next from @objcursor into @id,@value
end

close @objcursor
deallocate @objcursor

Also you can create a procedure which will return you a cursor taking the selection query as a parameter.

 CREATE PROCEDURE [dbo].[Gsp_Create_GenericCursor]
    /* Parameters */
    @vQuery        NVARCHAR(MAX)
    ,@Cursor    CURSOR VARYING OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    
    DECLARE 
        @vSQL        AS NVARCHAR(MAX)
    
    SET @vSQL = 'SET @Cursor = CURSOR FORWARD_ONLY STATIC FOR ' + @vQuery + ' OPEN @Cursor;'
    
   
    EXEC sp_executesql
         @vSQL
         ,N'@Cursor cursor output'  
         ,@Cursor OUTPUT;
END 

DECLARE @obj AS CURSOR
DECLARE @i AS INT    
 
 
    EXEC dbo.Gsp_Create_GenericCursor 
        @vQuery = N'SELECT 1 AS FLD1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4'
        ,@Cursor = @obj OUTPUT
        
        FETCH NEXT FROM @obj INTO @i
        
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            PRINT @i
            
            FETCH NEXT FROM @obj INTO @i
        END
        
        CLOSE @obj
        DEALLOCATE @obj