Click here to Skip to main content
Click here to Skip to main content

Tagged as

Create a Cursor using Dynamic SQL Query

, 6 Nov 2012
Rate this:
Please Sign up or sign in to vote.
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 
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 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
I have been in software industry for more than 8 years. I have developed different type of software using different languages. Many of them are database related (both web & window based), SQL being as the back end most of the time. Up-to-date I have knowledge in languages such as C#, VB.Net, T-SQL, JAVA, VB6 & C++, making C# the most proficient of all. Also I have worked using different technologies like ASP.Net, SharePoint, Crystal Reports (But I really hate designing reports) & MS SQL Server and have involved in designing & developing software for major companies like FedEx, Softlogic Holdings, IronOne Technologies & Brandix. Currently I am working as a Tech Lead in Singapore.
Follow on   Twitter   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 PinmemberLeonardoFiorot25-Jun-13 14:09 
QuestionFormatting... PinmvpSandeep Mewara6-Nov-12 0:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 6 Nov 2012
Article Copyright 2012 by Manjuke Fernando
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid