Dec 162011
 

Fetch statement allows you to retrieve one records at a time. If you need multiple records returned, you will have to call fetch multiple times to get multiple sets. Each returned sets has one record in it. It would be useful if a cursor can return multiple rows by one fetch. This can be implemented by API cursor.

use AdventureWorks
set nocount on
go
declare @handle int 
exec sp_cursoropen @handle output,'select cast(row_number() over(order by rowguid) as int)rownum, rowguid, cast(Name as varchar(10)) name from Sales.Store order by rowguid', @scrollopt=1, @ccopt=1 -- open cursor
exec sp_cursoroption @cursor = @handle, @code = 2, @cursorname = 'cursor_test'
print '....cursor opened....'
fetch next from cursor_test 
fetch next from cursor_test
print 'get cursor handle ' + cast(@handle as varchar(20))
print 'Fetch 3 records now'
exec sp_cursorfetch @handle, 2,  0, 3 
exec sp_cursorclose @handle
print '....cursor closed....'
go
rownum      rowguid                              name
----------- ------------------------------------ ----------

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

rownum      rowguid                              name
----------- ------------------------------------ ----------
2           D23B2B5F-D58A-4ADE-A204-0082F50E32BA Ultimate B

get cursor handle 180150019
Fetch 3 records now
rownum      rowguid                              name
----------- ------------------------------------ ----------
3           A37F70D2-4AA8-4C29-A3F4-00D238DD9628 Major Amus
4           824B931B-5F27-407C-81AD-00FF7BC886F1 Gasless Cy
5           5EFF72C4-6EA5-4CDC-8D14-017265283388 Leather an

....cursor closed....

There are few restrictions. You will have to use API cursor open procedure to open a cursor in order to use API cursor fetch to return multiple records. When you close a cursor, use API cursor close instead of using deallocate statement to close a cursor. This is useful when you are implementing pagination. Comparing to current paging technologies, such as Rownumber() over() and offset/fetch, this is the most efficient approach.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.