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
2016-10-17, 781👍, 0💬
Popular Posts:
Is SQL Server Transact-SQL case sensitive? No. Transact-SQL is not case sensitive. Like the standard...
How To Import One Table Back from a Dump File in Oracle? If you only want to import one table back t...
How to create new tables with "CREATE TABLE" statements in SQL Server? If you want to create a new t...
How To Convert Numeric Values to Integers in SQL Server Transact-SQL? Sometimes you need to round a ...
How to run Queries with "sqlcmd" tool in SQL Server? "sqlcmd" is a client tool that you can use to i...