Collections:
"@@FETCH_STATUS" - Looping through Result Set in SQL Server
How To Loop through the Result Set with @@FETCH_STATUS in SQL Server Transact-SQL?
✍: FYIcenter.com
The FETCH statement only returns one row from the result set. If you want to return all rows, you need to put the FETCH statement in a loop. A simple way to stop the loop to check the system variable @@FETCH_STATUS, which returns a status code of the last FETCH statement:
@status = @@FETCH_STATUS; -- Returns 0, the fetch was successful -- Returns -1, the fetch failed or end of result set reached -- Returns -2, the row fetched is missing
The tutorial exercise below shows how @@FETCH_STATUS is used to make WHILE loop over the result set in a cursor:
USE FyiCenterData; GO DECLARE fyi_cursor CURSOR FOR SELECT id, url, notes, counts, time FROM fyi_links; 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
⇒ Declaring and Using Cursor Variables in SQL Server
⇐ "FETCH" - Transferring Data from Cursors to Variables in SQL Server
2016-10-17, 1665🔥, 0💬
Popular Posts:
What Are Date and Time Functions in MySQL? MySQL offers a number of functions for date and time valu...
What Is ISAM in MySQL? ISAM (Indexed Sequential Access Method) was developed by IBM to store and ret...
What Is ISAM in MySQL? ISAM (Indexed Sequential Access Method) was developed by IBM to store and ret...
How To Break Query Output into Pages in MySQL? If you have a query that returns hundreds of rows, an...
How to download and install the scaled-down database AdventureWorksLT in SQL Server? If you want to ...