Collections:
DYNAMIC - Creating Dynamic Cursors in SQL Server
How To Create a Dynamic Cursor with the DYNAMIC Option in SQL Server Transact-SQL?
✍: FYIcenter.com
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.
Â
2016-10-17, 2992👍, 0💬
Popular Posts:
How REAL and FLOAT Literal Values Are Rounded in SQL Server Transact-SQL? By definition, FLOAT(n) sh...
Where to find answers to frequently asked questions on Storage Engines: MyISAM, InnoDB and BDB in My...
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...
How AdventureWorksLT tables are related in SQL Server? There are 12 user tables defined in Adventure...
What Happens to Your Transactions When ERROR 1205 Occurred in MySQL? If your transaction receives th...