How To Create a Scrollable Cursor with the SCROLL Option in SQL Server Transact-SQL?

SQL Server offers two scrolling option on cursors:

1. FORWARD_ONLY - The cursor can only be scrolled forward with "FETCH NEXT" statements. In another word, you can only loop through the cursor from the first row to the last row. FORWARD_ONLY is the default option.

2. SCROLL - The cursor can be scrolled back and forth with "FETCH NEXT", "FETCH PRIOR", and other fetch options.

The tutorial example below creates a cursor with the SCROLL option so that the result set can be looped through backward:

DECLARE @fyi_cursor CURSOR;
SET @fyi_cursor = CURSOR SCROLL FOR
  SELECT id, url, notes, counts, time
  FROM fyi_links ORDER BY id;
OPEN @fyi_cursor;
DECLARE @id INT, @url VARCHAR(80), @notes VARCHAR(80),
  @counts INT, @time DATETIME;
FETCH LAST FROM @fyi_cursor INTO @id, @url, @notes, 
  @counts, @time;
WHILE @@FETCH_STATUS = 0 BEGIN
  PRINT CONVERT(CHAR(5),ISNULL(@id,0))
    +CONVERT(CHAR(18),ISNULL(@url,'NULL'))
    +CONVERT(CHAR(20),ISNULL(@notes,'NULL'))
    +CONVERT(CHAR(4),ISNULL(@counts,0))
    +CONVERT(CHAR(11),ISNULL(@time,'2007'));
  FETCH PRIOR FROM @fyi_cursor INTO @id, @url, @notes, 
    @counts, @time;
  END
CLOSE @fyi_cursor;
DEALLOCATE @fyi_cursor;
GO
2102 dba.fyicenter.com NULL                0   Jan  1 2007
2101 dev.fyicenter.com NULL                0   Jan  1 2007
1101 moc.retneciyf.ved NULL                0   Jan  1 2007
302  myspace.com       Added today!        0   Jul  1 2007
301  netscape.com      Added long time ago!0   Jan  1 1999
202  www.yahoo.com     It's another search 0   Jan  1 2007
102  dba.fyicenter.com Nice site.          8   Jan  1 2007
101  dev.fyicenter.com NULL                0   Jan  1 2007

 

⇒Using Cursors in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials