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, 1761🔥, 0💬
Popular Posts:
How To Change the Password for Your Own User Account in MySQL? If you want to change the password of...
How to set the current database in SQL Server? Once you are connected to the SQL Server, you should ...
How AdventureWorksLT tables are related in SQL Server? There are 12 user tables defined in Adventure...
How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL? Sometime you want t...
What Happens If the Imported Table Already Exists in Oracle? If the import process tries to import a...