How To Create a Dynamic Cursor with the DYNAMIC Option in SQL Server Transact-SQL?

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 FyiCenterData;
GO

DECLARE @fyi_cursor CURSOR;
SET @fyi_cursor = CURSOR FOR
  SELECT id, url, notes, counts, time FROM fyi_links;
OPEN @fyi_cursor;
DECLARE @id INT, @url VARCHAR(80), @notes VARCHAR(80),
  @counts INT, @time DATETIME;
FETCH NEXT FROM @fyi_cursor INTO @id, @url, @notes, 
  @counts, @time;

-- Changing the underlying table
UPDATE fyi_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 @fyi_cursor INTO @id, @url, @notes, 
    @counts, @time;
  END
CLOSE @fyi_cursor;
DEALLOCATE @fyi_cursor;
GO

(8 row(s) affected)
101  dev.fyicenter.com NULL                0   Jan  1 2007
102  moc.retneciyf.abd Nice site.          8   Jan  1 2007
1101 dev.fyicenter.com NULL                0   Jan  1 2007
202  moc.oohay.www     It's another search 0   Jan  1 2007
2101 moc.retneciyf.ved NULL                0   Jan  1 2007
2102 moc.retneciyf.abd NULL                0   Jan  1 2007
301  moc.epacsten      Added long time ago!0   Jan  1 1999
302  moc.ecapsym       Added today!        0   Jun 30 2007

The changes are reflected in the result set.

 

⇒Using Cursors in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials