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

Friday, March 20, 2009 |

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

http://ritesh-a-shah.blogspot.com/2009/03/what-is-cursor-definition-of-cursor-in.html

http://ritesh-a-shah.blogspot.com/2009/03/forwardonly-and-fastforward-cursor.html

http://ritesh-a-shah.blogspot.com/2009/03/dynamic-cross-tab-query-with-cursor-in.html

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

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)


--INSERT records

INSERT INTO emps

SELECT 'Ritesh','MIS','echem' UNION ALL

SELECT 'Bihag', 'MIS', 'CT' UNION ALL

SELECT 'Rajan', 'account','Marwadi' UNION ALL

SELECT 'Alka','account','tata' UNION ALL

SELECT 'Alpesh','Chemical','echem'

GO


DECLARE @Counter INT

SET @Counter =1

DECLARE @strName VARCHAR(200)

DECLARE @empCursor CURSOR

--declaring SCROLL cursor

--which will move first, last, forward and backward

SET @empCursor = CURSOR SCROLL FOR

SELECT Name FROM emps ORDER BY NAME

OPEN @empCursor

FETCH NEXT FROM @empCursor INTO @strName

--user of FETCH NEXT

WHILE (@@FETCH_STATUS = 0)

BEGIN

PRINT @strName + ' NEXT'

FETCH NEXT FROM @empCursor INTO @strName

SET @Counter = @Counter + 1

END

--user of FETCH PRIOR

WHILE (@Counter > 1)

BEGIN

FETCH PRIOR FROM @empCursor INTO @strName

PRINT @strName + ' PRIOR'

SET @Counter = @Counter - 1

END

--user of FETCH FIRST

BEGIN

FETCH FIRST FROM @empCursor INTO @strName

PRINT @strName +' FIRST'

END

--user of FETCH LAST

BEGIN

FETCH LAST FROM @empCursor INTO @strName

PRINT @strName +' LAST'

END


CLOSE @empCursor

DEALLOCATE @empCursor

GO

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

Reference: Ritesh Shah/Rashmika Vaghela

0 comments: