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, 1446🔥, 0💬
Popular Posts:
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...
What Is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is ...
How To Count Rows with the COUNT(*) Function in SQL Server? If you want to count the number of rows,...
How to change the data type of an existing column with "ALTER TABLE" statements in SQL Server? Somet...
What Are Out-of-Range Errors with DATETIME values in SQL Server Transact-SQL? When you enter DATETIM...