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, 1444🔥, 0💬
Popular Posts:
What To Do If the StartDB.bat Failed to Start the XE Instance in Oracle? If StartDB.bat failed to st...
How To Update Multiple Rows with One UPDATE Statement in SQL Server? If the WHERE clause in an UPDAT...
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...
What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...
How To Get a List of All Tables with "sys.tables" View in SQL Server? If you want to see the table y...