Collections:
Declaring and Using Cursor Variables in SQL Server
How To Declare and Use Cursor Variables in SQL Server Transact-SQL?
✍: FYIcenter.com
There are two ways to representing a cursor:
1. A cursor name - A static name representing a cursor object. A cursor name should be linked to a cursor object in the DECLARE statement.
2. A cursor variable name - A variable name pointing to a cursor object. A cursor variable name should be declared with the CURSOR data type. It should be then assigned with a cursor object using the SET statement.
The tutorial exercise below shows you how to declare a cursor variable and assign a cursor object to it:
USE FyiCenterData; GO -- declare a cursor variable DECLARE @fyi_cursor CURSOR; -- assign a cursor object SET @fyi_cursor = CURSOR FOR SELECT id, url, notes, counts, time FROM fyi_links; -- execute the query and fetch results OPEN @fyi_cursor; DECLARE @id INT, @url VARCHAR(80), @notes VARCHAR(80), @counts INT, @time DATETIME; FETCH NEXT 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 NEXT FROM @fyi_cursor INTO @id, @url, @notes, @counts, @time; END CLOSE @fyi_cursor; DEALLOCATE @fyi_cursor; GO 101 dev.fyicenter.com NULL 0 Jan 1 2007 102 dba.fyicenter.com Nice site. 8 Jan 1 2007 1101 moc.retneciyf.ved NULL 0 Jan 1 2007 202 www.yahoo.com It's another search 0 Jan 1 2007 2101 dev.fyicenter.com NULL 0 Jan 1 2007 2102 dba.fyicenter.com NULL 0 Jan 1 2007 301 netscape.com Added long time ago!0 Jan 1 1999 302 myspace.com Added today! 0 Jul 1 2007
⇒ SCROLL - Creating Cursors for Backward Scrolling in SQL Server
⇐ "@@FETCH_STATUS" - Looping through Result Set in SQL Server
2016-10-17, 1488🔥, 0💬
Popular Posts:
How To Start Instance with a Minimal Initialization Parameter File in Oracle? The sample initializat...
How To Convert Numeric Values to Integers in SQL Server Transact-SQL? Sometimes you need to round a ...
How Fixed Length Strings Are Truncated and Padded in SQL Server Transact-SQL? When the length of the...
What are binary literals supported in SQL Server Transact-SQL? Binary literals in Transact-SQL are s...
How To Connect the Oracle Server as SYSDBA in Oracle? This is Step 4. The best way to connect to the...