How To Create a Dynamic Cursor with the DYNAMIC Option?
Submitted by: AdministratorIf 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
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
<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
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Start SQL Server Browser Service? |
☺ | How To Execute the Cursor Queries with "OPEN" Statements? |
☺ | How To Test ODBC DSN Connection Settings? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How To Create a Testing Table with Test Data in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |