MS SQL Server Concepts and Programming Question:
Download Questions PDF

How To Loop through the Result Set with @@FETCH_STATUS?

Answers:

Answer #1
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 GlobalGuideLineDatabase;
GO

Answer #2
DECLARE ggl_cursor CURSOR FOR
SELECT id, url, notes, counts, time FROM ggl_links;
OPEN ggl_cursor;
DECLARE @id INT, @url VARCHAR(80), @notes VARCHAR(80),
@counts INT, @time DATETIME;
FETCH NEXT FROM ggl_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 ggl_cursor INTO @id, @url, @notes,
@counts, @time;
END
CLOSE ggl_cursor;
DEALLOCATE ggl_cursor;
GO
101  globalguideline.com      NULL                0   Jan  1 2007
102 globalguideline.com/sql Nice site. 8 Jan 1 2007
1101 globalguideline.com/xml NULL 0 Jan 1 2007
202 www.google.com It's another search 0 Jan 1 2007
2101 globalguideline.com/seo NULL 0 Jan 1 2007
2102 globalguideline.com/html NULL 0 Jan 1 2007
301 netscape.com Added long time ago!0 Jan 1 1999
302 yahoo.com Added today! 0 Jul 1 2007


Download MS SQL Server Interview Questions And Answers PDF

Previous QuestionNext Question
How To Transfer Data from a Cursor to Variables with a "FETCH" Statement?How To Declare and Use Cursor Variables?