Click here to Skip to main content
12,453,873 members (73,454 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

51.8K views
5 bookmarked
Posted

Create a Cursor using Dynamic SQL Query

, 6 Nov 2012 CPOL
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
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionMy vote of 5 Pin
Rocky_UK5-Aug-15 22:36
memberRocky_UK5-Aug-15 22:36 
GeneralMy vote of 5 Pin
LeonardoFiorot25-Jun-13 14:09
memberLeonardoFiorot25-Jun-13 14:09 
QuestionFormatting... Pin
Sandeep Mewara6-Nov-12 0:19
mvpSandeep Mewara6-Nov-12 0:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web01 | 2.8.160826.1 | Last Updated 6 Nov 2012
Article Copyright 2012 by Manjuke Fernando
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid