Collections:
SCROLL - Creating Cursors for Backward Scrolling in SQL Server
How To Create a Scrollable Cursor with the SCROLL Option in SQL Server Transact-SQL?
✍: FYIcenter.com
SQL Server offers two scrolling option on cursors:
1. FORWARD_ONLY - The cursor can only be scrolled forward with "FETCH NEXT" statements. In another word, you can only loop through the cursor from the first row to the last row. FORWARD_ONLY is the default option.
2. SCROLL - The cursor can be scrolled back and forth with "FETCH NEXT", "FETCH PRIOR", and other fetch options.
The tutorial example below creates a cursor with the SCROLL option so that the result set can be looped through backward:
DECLARE @fyi_cursor CURSOR; SET @fyi_cursor = CURSOR SCROLL FOR SELECT id, url, notes, counts, time FROM fyi_links ORDER BY id; OPEN @fyi_cursor; DECLARE @id INT, @url VARCHAR(80), @notes VARCHAR(80), @counts INT, @time DATETIME; FETCH LAST FROM @fyi_cursor INTO @id, @url, @notes, @counts, @time; 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 PRIOR FROM @fyi_cursor INTO @id, @url, @notes, @counts, @time; END CLOSE @fyi_cursor; DEALLOCATE @fyi_cursor; GO 2102 dba.fyicenter.com NULL 0 Jan 1 2007 2101 dev.fyicenter.com NULL 0 Jan 1 2007 1101 moc.retneciyf.ved NULL 0 Jan 1 2007 302 myspace.com Added today! 0 Jul 1 2007 301 netscape.com Added long time ago!0 Jan 1 1999 202 www.yahoo.com It's another search 0 Jan 1 2007 102 dba.fyicenter.com Nice site. 8 Jan 1 2007 101 dev.fyicenter.com NULL 0 Jan 1 2007
⇒ DYNAMIC - Creating Dynamic Cursors in SQL Server
⇐ Declaring and Using Cursor Variables in SQL Server
2016-10-17, 1687🔥, 0💬
Popular Posts:
How To Find Out What Privileges a User Currently Has in Oracle? Privileges granted to users are list...
What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initializatio...
How To Convert Binary Strings into Integers in SQL Server Transact-SQL? Binary strings and integers ...
How To Convert Numeric Values to Character Strings in MySQL? You can convert numeric values to chara...
How To Connect ASP Pages to Oracle Servers in Oracle? If you are running Windows IIS Web server and ...