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, 2051🔥, 0💬
Popular Posts:
How To Convert Numeric Values to Integers in SQL Server Transact-SQL? Sometimes you need to round a ...
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...
Can Date and Time Values Be Converted into Integers in SQL Server Transact-SQL? Can date and time va...
How To Divide Query Output into Multiple Groups with the GROUP BY Clause in SQL Server? Sometimes, y...
What Is an Oracle Tablespace in Oracle? An Oracle tablespace is a big unit of logical storage in an ...