Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

FETCH

SQL Server 2000

Retrieves a specific row from a Transact-SQL server cursor.

Syntax

FETCH
        [ [ NEXT | PRIOR | FIRST | LAST
                | ABSOLUTE { n | @nvar }
                | RELATIVE { n | @nvar }
            ]
            FROM
        ]
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
[ INTO @variable_name [ ,...n ] ]

Arguments

NEXT

Returns the result row immediately following the current row, and increments the current row to the row returned. If FETCH NEXT is the first fetch against a cursor, it returns the first row in the result set. NEXT is the default cursor fetch option.

PRIOR

Returns the result row immediately preceding the current row, and decrements the current row to the row returned. If FETCH PRIOR is the first fetch against a cursor, no row is returned and the cursor is left positioned before the first row.

FIRST

Returns the first row in the cursor and makes it the current row.

LAST

Returns the last row in the cursor and makes it the current row.

ABSOLUTE {n | @nvar}

If n or @nvar is positive, returns the row n rows from the front of the cursor and makes the returned row the new current row. If n or @nvar is negative, returns the row n rows before the end of the cursor and makes the returned row the new current row. If n or @nvar is 0, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.

RELATIVE {n | @nvar}

If n or @nvar is positive, returns the row n rows beyond the current row and makes the returned row the new current row. If n or @nvar is negative, returns the row n rows prior to the current row and makes the returned row the new current row. If n or @nvar is 0, returns the current row. If FETCH RELATIVE is specified with n or @nvar set to negative numbers or 0 on the first fetch done against a cursor, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.

GLOBAL

Specifies that cursor_name refers to a global cursor.

cursor_name

Is the name of the open cursor from which the fetch should be made. If both a global and a local cursor exist with cursor_name as their name, cursor_name to the global cursor if GLOBAL is specified and to the local cursor if GLOBAL is not specified.

@cursor_variable_name

Is the name of a cursor variable referencing the open cursor from which the fetch should be made.

INTO @variable_name[,...n]

Allows data from the columns of a fetch to be placed into local variables. Each variable in the list, from left to right, is associated with the corresponding column in the cursor result set. The data type of each variable must either match or be a supported implicit conversion of the data type of the corresponding result set column. The number of variables must match the number of columns in the cursor select list.

Remarks

If the SCROLL option is not specified in an SQL-92 style DECLARE CURSOR statement, NEXT is the only FETCH option supported. If SCROLL is specified in an SQL-92 style DECLARE CURSOR, all FETCH options are supported.

When the Transact_SQL DECLARE cursor extensions are used, these rules apply:

  • If either FORWARD-ONLY or FAST_FORWARD is specified, NEXT is the only FETCH option supported.

  • If DYNAMIC, FORWARD_ONLY or FAST_FORWARD are not specified, and one of KEYSET, STATIC, or SCROLL are specified, all FETCH options are supported.

  • DYNAMIC SCROLL cursors support all the FETCH options except ABSOLUTE.

The @@FETCH_STATUS function reports the status of the last FETCH statement. The same information is recorded in the fetch_status column in the cursor returned by sp_describe_cursor. This status information should be used to determine the validity of the data returned by a FETCH statement prior to attempting any operation against that data. For more information, see @@FETCH_STATUS.

Permissions

FETCH permissions default to any valid user.

Examples
A. Use FETCH in a simple cursor

This example declares a simple cursor for the rows in the authors table with a last name beginning with B, and uses FETCH NEXT to step through the rows. The FETCH statements return the value for the column specified in the DECLARE CURSOR as a single-row result set.

USE pubs
GO
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors
WHERE au_lname LIKE 'B%'
ORDER BY au_lname

OPEN authors_cursor

-- Perform the first fetch.
FETCH NEXT FROM authors_cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM authors_cursor
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

au_lname                                 
---------------------------------------- 
Bennet                                   
au_lname                                 
---------------------------------------- 
Blotchet-Halls                           
au_lname                                 
----------------------------------------
B. Use FETCH to store values in variables

This example is similar to the last example, except the output of the FETCH statements is stored in local variables rather than being returned directly to the client. The PRINT statement combines the variables into a single string and returns them to the client.

USE pubs
GO

-- Declare the variables to store the values returned by FETCH.
DECLARE @au_lname varchar(40), @au_fname varchar(20)


DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname FROM authors
WHERE au_lname LIKE 'B%'
ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. 

FETCH NEXT FROM authors_cursor
INTO @au_lname, @au_fname

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

   -- Concatenate and display the current values in the variables.
   PRINT 'Author: ' + @au_fname + ' ' +  @au_lname

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM authors_cursor
   INTO @au_lname, @au_fname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

Author: Abraham Bennet
Author: Reginald Blotchet-Halls
C. Declare a SCROLL cursor and use the other FETCH options

This example creates a SCROLL cursor to allow full scrolling capabilities through the LAST, PRIOR, RELATIVE, and ABSOLUTE options.

USE pubs
GO

-- Execute the SELECT statement alone to show the 
-- full result set that is used by the cursor.
SELECT au_lname, au_fname FROM authors
ORDER BY au_lname, au_fname

-- Declare the cursor.
DECLARE authors_cursor SCROLL CURSOR FOR
SELECT au_lname, au_fname FROM authors
ORDER BY au_lname, au_fname

OPEN authors_cursor

-- Fetch the last row in the cursor.
FETCH LAST FROM authors_cursor

-- Fetch the row immediately prior to the current row in the cursor.
FETCH PRIOR FROM authors_cursor

-- Fetch the second row in the cursor.
FETCH ABSOLUTE 2 FROM authors_cursor

-- Fetch the row that is three rows after the current row.
FETCH RELATIVE 3 FROM authors_cursor

-- Fetch the row that is two rows prior to the current row.
FETCH RELATIVE -2 FROM authors_cursor

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

au_lname                                 au_fname             
---------------------------------------- -------------------- 
Bennet                                   Abraham              
Blotchet-Halls                           Reginald             
Carson                                   Cheryl               
DeFrance                                 Michel               
del Castillo                             Innes                
Dull                                     Ann                  
Green                                    Marjorie             
Greene                                   Morningstar          
Gringlesby                               Burt                 
Hunter                                   Sheryl               
Karsen                                   Livia                
Locksley                                 Charlene             
MacFeather                               Stearns              
McBadden                                 Heather              
O'Leary                                  Michael              
Panteley                                 Sylvia               
Ringer                                   Albert               
Ringer                                   Anne                 
Smith                                    Meander              
Straight                                 Dean                 
Stringer                                 Dirk                 
White                                    Johnson              
Yokomoto                                 Akiko                

au_lname                                 au_fname             
---------------------------------------- -------------------- 
Yokomoto                                 Akiko                
au_lname                                 au_fname             
---------------------------------------- -------------------- 
White                                    Johnson              
au_lname                                 au_fname             
---------------------------------------- -------------------- 
Blotchet-Halls                           Reginald             
au_lname                                 au_fname             
---------------------------------------- -------------------- 
del Castillo                             Innes                
au_lname                                 au_fname             
---------------------------------------- -------------------- 
Carson                                   Cheryl

See Also

CLOSE

Cursors

DEALLOCATE

DECLARE CURSOR

OPEN

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft