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, 1760🔥, 0💬
Popular Posts:
What Are the Underflow and Overflow Behaviors on FLOAT Literals in SQL Server Transact-SQL? If you e...
What are single-byte character string data types supported in SQL Server Transact-SQL? Single-byte c...
How To Query Tables and Loop through the Returning Rows in MySQL? The best way to query tables and l...
How To Set Up SQL*Plus Output Format in Oracle? If you want to practice SQL statements with SQL*Plus...
How To Fix the INSERT Command Denied Error in MySQL? The reason for getting the "1142: INSERT comman...