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, 4820🔥, 0💬
Popular Posts:
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference...
What Happens to Your Transactions When ERROR 1205 Occurred in MySQL? If your transaction receives th...
How To Count Rows with the COUNT(*) Function in SQL Server? If you want to count the number of rows,...
What Happens If the Imported Table Already Exists in Oracle? If the import process tries to import a...
Where to find MySQL database server tutorials? Here is a collection of tutorials, tips and FAQs for ...