Open a Cursor over Queries, Procesures, and Dynamic SQL

Cursor is a built-in mechanism SQL Server provides to iterate through records returned frome a query even a stored procedure. Before using a cursor, the cursor has to be defined with a unique name in a scope, local and global. Cursor structure is built when it’s opened. Once the cursor gets opened for a set of rows, you can use fetch command to retrieve one row at a time and save it into a variable, return to client, or insert them into a table structure.

Declare a cursor:

use AdventureWorks
declare c cursor for
 select row_number() over(order by rowguid) rownum, rowguid, Name
 from Sales.Store order by rowguid

Open a cursor:

open c

At this point, the cursor is opened. This mean that the SQL that specified in the declaration of the cursor will be executed.
Fetch a record to client:

fetch next from c

rownum      rowguid                              name
----------- ------------------------------------ ----------
1           276C70D2-C4D6-458E-9A49-0036B93BB544 Trailblazi

(1 row(s) affected)

Fetch a record to variables:

declare @rownum int, @rowguid uniqueidentifier, @name varchar(10)
fetch next from c into @rownum, @rowguid, @name
select @rownum, @rowguid, @name

                                                
----------- ------------------------------------ ----------
2           D23B2B5F-D58A-4ADE-A204-0082F50E32BA Ultimate B

(1 row(s) affected)

Fetch a record to a temporary table:

create table #t(rownum int, rowguid uniqueidentifier, name varchar(10))
insert into #t
 exec('fetch next from c ')
select * from #t

(1 row(s) affected)
rownum      rowguid                              name
----------- ------------------------------------ ----------
3           A37F70D2-4AA8-4C29-A3F4-00D238DD9628 Major Amus

(1 row(s) affected)

Fetch a record to a memory table:

declare @t table(id int, rowguid uniqueidentifier, name varchar(10) )
insert into @t
 exec('fetch next from c ')
select * from @t

(1 row(s) affected)
id          rowguid                              name
----------- ------------------------------------ ----------
4           824B931B-5F27-407C-81AD-00FF7BC886F1 Gasless Cy

(1 row(s) affected)

Close and de-allocate a cursor:

close c
deallocate c

You could use @@fetch_status global variable to check the success of cursor operation. When this variable returns 0, it means Fetch statement is successful. When -1, FETCH failed. When -2, record is missing. The typical code of using cursor is

use AdventureWorks
declare @rownum int, @rowguid uniqueidentifier, @name varchar(10)
declare c cursor for
 select cast(row_number() over(order by rowguid) as int)rownum, rowguid, cast(Name as varchar(10)) name
 from Sales.Store order by rowguid
open c
fetch next from c into @rownum, @rowguid, @name
while @@fetch_status = 0
begin
 /*your code*/
 fetch next from c into @rownum, @rowguid, @name
end
close c
deallocate c

As I mentioned at beginning of this article, a stored procedure could be opened by cursor, how it works? API cursor procedures have to be called. I first of all open a cursor by using API cursor and then give it a name and finally use regular cursor statement operate it.

use AdventureWorks
set nocount on
go
create procedure test
as
 select cast(row_number() over(order by rowguid) as int)rownum, rowguid, cast(Name as varchar(10)) name
 from Sales.Store order by rowguid
go
declare @handle int
exec sp_cursoropen @handle output,'exec test', @scrollopt=1, @ccopt=1 -- open cursor
print '....cursor opened....'
exec sp_cursoroption @cursor = @handle, @code = 2, @cursorname = 'cursor_test' -- set cursor name
fetch next from cursor_test -- fetch next
declare @rownum int, @rowguid uniqueidentifier, @name varchar(10)
fetch next from cursor_test into @rownum, @rowguid, @name -- fetch into variables
select @rownum, @rowguid, @name
close cursor_test
deallocate cursor_test
print '....cursor closed....'
go
--clean up
drop procedure test
go

rownum      rowguid                              name
----------- ------------------------------------ ----------

....cursor opened....
rownum      rowguid                              name
----------- ------------------------------------ ----------
1           276C70D2-C4D6-458E-9A49-0036B93BB544 Trailblazi

                                                
----------- ------------------------------------ ----------
2           D23B2B5F-D58A-4ADE-A204-0082F50E32BA Ultimate B

....cursor closed....