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.
⇒ SQL Server Transact-SQL Tutorials
⇐ SCROLL - Creating Cursors for Backward Scrolling in SQL Server
2016-10-17, 5123🔥, 0💬
Popular Posts:
How to execute statements under given conditions in SQL Server Transact-SQL? How to use IF ... ELSE ...
How To Enter Unicode Character String Literals in SQL Server Transact-SQL? Unicode characters are mu...
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...
What Is an Oracle Tablespace in Oracle? An Oracle tablespace is a big unit of logical storage in an ...
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...