MS SQL Server Interview Question:

How To Create a Scrollable Cursor with the SCROLL Option?

Submitted by: Administrator

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 @ggl_cursor CURSOR;
SET @ggl_cursor = CURSOR SCROLL FOR
SELECT id, url, notes, counts, time
FROM ggl_links ORDER BY id;
OPEN @ggl_cursor;
DECLARE @id INT, @url VARCHAR(80), @notes VARCHAR(80),
@counts INT, @time DATETIME;
FETCH LAST FROM @ggl_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 @ggl_cursor INTO @id, @url, @notes,
@counts, @time;
END
CLOSE @ggl_cursor;
Submitted by: Administrator

DEALLOCATE @ggl_cursor;
<pre>GO
2102 globalguideline.com/html NULL 0 Jan 1 2007
2101 globalguideline.com/seo NULL 0 Jan 1 2007
1101 globalguideline.com/xml NULL 0 Jan 1 2007
302 google.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 globalguideline.com/sql Nice site. 8 Jan 1 2007
101 globalguideline.com NULL 0 Jan 1 2007</pre>

Submitted by: Administrator