Monday 5 November 2012

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 situation. But there are times you are required to build the cursor using a dynamic SQL select statement.

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


 


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


Use the following syntax to use the above mentioned procedure



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