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
2016-10-17, 702👍, 0💬
Popular Posts:
How To Download Oracle Database 10g XE in Oracle? If you want to download a copy of Oracle Database ...
How To Convert Character Strings into Numeric Values in SQL Server Transact-SQL? Sometimes you need ...
How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...
How To Get Year, Month and Day Out of DATETIME Values in SQL Server Transact-SQL? You can use DATEPA...
How To Format DATETIME Values to Strings with the CONVERT() Function in SQL Server Transact-SQL? SQL...