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