Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - "FETCH" - Transferring Data from Cursors to Variables

By: FYIcenter.com

(Continued from previous topic...)

How To Transfer Data from a Cursor to Variables with a "FETCH" Statement?

By default, a FETCH statement will display the fetched row on the client program window. If you want to transfer the output data to variables, you can specify an INTO clause with a list of variables that matches the list of fields in the result set.

The tutorial exercise below shows you a good example of using the FETCH statement to transfer one row of output data from the result set to variables:

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;
PRINT 'id = '+CONVERT(VARCHAR(20),ISNULL(@id,0));
PRINT 'url = '+ISNULL(@url,'NULL');
PRINT 'notes = '+ISNULL(@notes,'NULL');
PRINT 'counts = '+CONVERT(VARCHAR(20),ISNULL(@counts,0));
PRINT 'time = '+CONVERT(VARCHAR(20),ISNULL(@time,'2007'));

CLOSE fyi_cursor;
DEALLOCATE fyi_cursor;
GO

id = 101
url = dev.fyicenter.com
notes = NULL
counts = 0
time = Jan  1 2007 12:00AM

(Continued on next topic...)

1. What Are Cursors?
2. How To Declare a Cursor with "DECLARE ... CURSOR"?
3. How To Execute the Cursor Queries with "OPEN" Statements?
4. How To Fetch the Next Row from a Cursor with a "FETCH" Statement?
5. How To Transfer Data from a Cursor to Variables with a "FETCH" Statement?
6. How To Loop through the Result Set with @@FETCH_STATUS?
7. How To Declare and Use Cursor Variables?
8. How To Create a Scrollable Cursor with the SCROLL Option?
9. How To Create a Dynamic Cursor with the DYNAMIC Option?