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, 1635🔥, 0💬
Popular Posts:
Where to find SQL Server Transact-SQL language references? You can find SQL Server Transact-SQL lang...
How To Get the Definition of a Stored Procedure Back in SQL Server Transact-SQL? If you want get the...
How To Connect ASP Pages to Oracle Servers in Oracle? If you are running Windows IIS Web server and ...
What Happens If the UPDATE Subquery Returns Multiple Rows in MySQL? If a subquery is used in a UPDAT...
How To Generate CREATE VIEW Script on an Existing View in SQL Server? If you want to know how an exi...