Scrollable Dynamic Cursor with FETCH_FIRST, FETCH_LAST, FETCH_PRIOR, FETCH_NEXT in SQL Server 2005

This is continuous topic of CURSOR. You can refer my past article on cursor at

I gave definition of CURSOR and explained different type of CURSOR in my first article given above. Second article was for FORWARD_ONLY cursor and third one was for dynamic cross tab query with FAST_FORWARD cursor. Now this article will show you example of dynamic cursor which can move forward and backward as well. It will get fresh record set with every FETCH statement so that while fetching the record, we will be able to get fresh modified record by another user for our SELECT statement in SQL Server 2005.

Let us see it practically.

–create one table for demo

use adventureworks



Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)


–INSERT records


SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL


SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’



SET @Counter =1



–declaring SCROLL cursor

–which will move first, last, forward and backward



OPEN @empCursor

FETCH NEXT FROM @empCursor INTO @strName

–user of FETCH NEXT



PRINT @strName + NEXT’

FETCH NEXT FROM @empCursor INTO @strName

SET @Counter = @Counter + 1


–user of FETCH PRIOR

WHILE (@Counter > 1)


FETCH PRIOR FROM @empCursor INTO @strName

PRINT @strName + PRIOR’

SET @Counter = @Counter 1


–user of FETCH FIRST


FETCH FIRST FROM @empCursor INTO @strName

PRINT @strName + FIRST’


–user of FETCH LAST


FETCH LAST FROM @empCursor INTO @strName

PRINT @strName + LAST’


CLOSE @empCursor



Note: this is very resource consuming cursor so think twice before use it.

Reference: Ritesh Shah/Rashmika Vaghela

What is CURSOR? Definition of CURSOR in SQL Server 2005:

CURSOR is a server side tool and completely different from ADO.NET’s cursor. It is giving row-by-row solution to the result set and let me tell you that SQL Server impressive with handling set of rows, not row-by-row. This is useful for those who came from procedural background and don’t much familiar with set-based relational algebra.

You can define CURSOR as read only or update but read only is fast as compare with update as read only will gives your data and won’t remember it.

Personally I used to avoid cursor as long as it is possible as it uses lots of resources of server and reduce the performance. Whenever it is possible use temp table, derived table, sub-query, CASE statement but finally you draw conclusion that any of the set-based operation won’t work for your problem than and than go for CURSOR solution but make sure you are not selecting more than necessary rows in CURSOR. Lesser the row, higher the performance!!!!

The main reason for writing this article is to help those who want to understand the concept of CURSOR and another reason CURSOR is not something that you can completely ignore. There may be some situation where you have to use CURSOR, may be some complex logic or dynamic code iteration especially while making code generator.

Type of CURSOR:

Static: This is lowest type of CURSOR and used to use for finding data and generating reports. Once getting the data into the CURSOR you will not identify any modification done in data after retrieving it because it make a copy of your data into the temp table of tempDB.

Forward_Only: This is the default type of CURSOR and really very similar to Static CURSOR the only difference you will find it, it will move forward only. In short, this CURSOR will scroll from first to last no other movement supported.

Fast_Forward: this is a mixture of Forward_Only and Read_Only.

Dynamic: this CURSOR will be bit slow as it will accept any data modification done by any user even after you fetch the data because you scroll around the CURSOR. Data membership, value and its order will be changed in each FETCH if any data modification has been done in record set.

Keyset-Driven: when you open this CURSOR, membership key and order of row are fixed in CURSOR.

Steps for CURSOR:

DECLARE: Defines a CURSOR with standard SELECT statement. This must be done before you open the CURSOR.

OPEN: physically open the CURSOR and received the record set exist in table at the time of opening the CURSOR.

FETCH: CURSOR always points to one row at a time and FETCH is retrieve the value from that row to manipulate it further.

CLOSE: CLOSE will release the lock on table made by CURSOR. If you wish than you can re-open CURSOR after closes it.

DEALLOCATE: Once you are done with CURSOR, do DEALLOCATE it and removes the memory from the server. You can open the CURSOR once you close it but can’t re-open CURSOR once you DEALLOCATE it.

You can run following query for dynamic management function in-order to get information about CURSOR running on your server.

select * from sys.dm_exec_cursors(0)

I will give examples to make your concept more clearly about different type of CURSOR in my future article.

Reference: Ritesh Shah