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, 1651🔥, 0💬
Popular Posts:
How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...
What Happens to Your Transactions When ERROR 1205 Occurred in MySQL? If your transaction receives th...
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...
How To Insert New Line Characters into Strings in SQL Server Transact-SQL? If you want to break a st...