MS SQL Server Interview Question:

How To Create a Dynamic Cursor with the DYNAMIC Option?

Submitted by: Administrator

If the underlying table is changed after the cursor is opened, should the changes be reflected in the cursor result set? The answer is based on the update option used when creating the cursor. SQL SERVER supports two update options:

1. STATIC - The result set will be a static copy created when the OPEN statement is executed. Subsequent updates on the underlying tables will not affect the result set. STATIC is the default option.

2. SCROLL - The result set will be dynamically updated each time when a FETCH statement is executed. In another word, the result set always reflects the latest changes on the underlying tables.

The tutorial script below gives you a good example of how dynamic cursors work:

USE GlobalGuideLineDatabase;
GO

DECLARE @ggl_cursor CURSOR;
SET @ggl_cursor = CURSOR FOR
SELECT id, url, notes, counts, time FROM ggl_links;
OPEN @ggl_cursor;
DECLARE @id INT, @url VARCHAR(80), @notes VARCHAR(80),
@counts INT, @time DATETIME;
FETCH NEXT FROM @ggl_cursor INTO @id, @url, @notes,
@counts, @time;
Submitted by: Administrator

-- Changing the underlying table
<pre>UPDATE ggl_links SET url=REVERSE(url);
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 NEXT FROM @ggl_cursor INTO @id, @url, @notes,
@counts, @time;
END
CLOSE @ggl_cursor;
DEALLOCATE @ggl_cursor;
GO
(8 row(s) affected)
101 globalguideline.com NULL 0 Jan 1 2007
102 globalguideline.com/sql Nice site. 8 Jan 1 2007
1101 globalguideline.com/xml NULL 0 Jan 1 2007
202 www.google.com It's another search 0 Jan 1 2007
2101 globalguideline.com/seo NULL 0 Jan 1 2007
2102 globalguideline.com/html NULL 0 Jan 1 2007
301 netscape.com Added long time ago!0 Jan 1 1999
302 yahoo.com Added today! 0 Jul 1 2007</pre>
The changes are reflected in the result set.
Submitted by: Administrator