How To Loop through the Result Set with @@FETCH_STATUS in SQL Server Transact-SQL?

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

⇑ Using Cursors in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials