Structured Query Language

                  Fetch





 
Retrieves a specific row from the cursor.
 
Syntax:
 
FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n] FROM]
 cursor_name 
[INTO @variable_name1, @variable_name2, ...]
where

Note:
  NEXT, PRIOR, FIRST, LAST, ABSOLUTE n, and RELATIVE n are
  available only with MS-SQL Server and not DB2

  
NEXT
  Returns the first row of the results set if this is the first fetch
  against the cursor; otherwise, it moves the cursor one row within the
  results set. NEXT is the primary method used to move through a results
  set. NEXT is the default cursor fetch.
 
Note:
  PRIOR, FIRST, LAST, ABSOLUTE n, and RELATIVE n are available only
  with cursors defined with the SCROLL option.




 
PRIOR
  Returns the previous row within the results set.
 
FIRST
  Moves the cursor to the first row within the results set and returns
  the first row.
 
LAST
  Moves the cursor to the last row within the results set and returns
  the last row.
 
ABSOLUTE n
  Returns the nth row within the results set. If n is a negative value,
  the returned row will be the nth row counting backward from the last
  row of the results set.
 
RELATIVE n
  Returns the nth row after the currently fetched row. If n is a negative
  value, the returned row will be the nth row counting backward from
  the relative position of the cursor.
 
FROM cursor_name
  Defines the cursor from which the fetch should be made. Multiple
  cursors are allowed within any session provided that each has a unique
  name.
  
INTO @variable_name1, @variable_name2, ...
  Allows data returned within a fetch to be placed into local variables.
  Each of the variables must match the datatype. Errors will occur when
  the datatypes are incompatible. Implicit datatype conversions are not
  provided here.






 
A global variable, @@FETCH_STATUS, will be updated at every execution
of FETCH. At a successful fetch, @@FETCH_STATUS will be set to 0. If no
data was fetched because the requested cursor position exceeded the
results set, -1 will be returned. If the row returned is no longer a
member of the results set (for example, the row was deleted from the
base table after the cursor was opened), @@FETCH_STATUS will return -2.
Always use this to determine the validity of the data returned from a
cursor fetch prior to attempting any operation against that data.
  

SQL Tutorial & Tips
Select
Insert
Update
Delete
Open Cursor
Fetch
Close Cursor
Union