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, 1813🔥, 0💬
Popular Posts:
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...
What Are the Underflow and Overflow Behaviors on FLOAT Literals in SQL Server Transact-SQL? If you e...
How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL? Sometime you want t...
What Is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is ...
How To Convert Character Strings into Numeric Values in SQL Server Transact-SQL? Sometimes you need ...