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, 1698🔥, 0💬
Popular Posts:
How To Convert a Unicode Strings to Non-Unicode Strings in SQL Server Transact-SQL? Since Unicode ch...
How To Enter Unicode Character String Literals in SQL Server Transact-SQL? Unicode characters are mu...
How To List All Login Names on the Server in SQL Server? If you want to see a list of all login name...
How Fixed Length Strings Are Truncated and Padded in SQL Server Transact-SQL? When the length of the...
How To Install PHP on Windows in MySQL? The best way to download and install PHP on Windows systems ...