FETCH Statement (version 6.5)

Accepts variables and procedure parameters for the row number arguments to the ABSOLUTE and RELATIVE keywords.

For additional syntax information for the FETCH statement, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

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

where

NEXT

Specifies that the first row of the results set is returned if this is the first fetch against the cursor; otherwise, it moves the cursor one row forward within the results set. NEXT is the primary method to move through the results set; it is also the default cursor fetch action.

PRIOR

Specifies that the previous row within the results set is returned.

FIRST

Specifies that the cursor is moved to the first row within the results set and returns the first row.

LAST

Specifies that the cursor is moved to the last row within the results set and returns the last row.

ABSOLUTE {n | @nvar}

Specifies that the nth row within the results set is returned. If n is a negative value, the returned row will be the nth row counting backward from the last row of the results set. Only the smallint, tinyint and int datatypes are supported for n.

RELATIVE {n | @nvar}

Specifies that the nth row after the currently fetched row is returned. If n is a negative value, the returned row will be the nth row counting backward from the relative position of the cursor. Only the smallint, tinyint and int datatypes are supported for n.

FROM cursor_name

Specifies 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, ...

Specifies a variable in which to place data that is returned by a fetch. Each of the variable datatypes must match the datatype returned by the fetch. Errors occur when the datatypes are incompatible.

Remarks

This statement retrieves a specific row from the cursor. Variables can be used for row number arguments. For example, the FETCH ABSOLUTE statement can be used with a variable to find a particular row within the cursor. For example, the row this statement retrieves depends upon the value of the @row_num variable:

FETCH ABSOLUTE @row_num FROM customer_cursor
  

By using variables for row number argument names, the program can provide the value at run time. This is useful because the row number value can be dependent upon run-time data that is available only at run time. This offers greater flexibility for programming the FETCH statement.

Example

This example calculates the median number of personnel in the various departments of a specific company. The company name is specified in the procedure. To calculate the median number of employees in a specific department, follow these steps:

1. For the company specified, order the departments by population into a department list.
2. Determine if the number of departments is odd or even.
   • If the number of departments is even, the median value is derived from the two departments' population values that appear on either side of the middle of the department list. Add the two population values together and divide by two.
   • If the number of departments is odd, the median value is derived from the population value that appears in the middle of the company list.

For this example, assume that a database exists that holds all departments' populations for all companies in the Algodata Infosystems conglomerate.

/*Create procedure*/
CREATE PROCEDURE find_median_population @company varchar(25)
AS
DECLARE @num_departments int
DECLARE @value1 int
DECLARE @value2 int
DECLARE @middle int
  
/* Declare cursor on population table */
DECLARE population_cursor SCROLL CURSOR
FOR select population 
FROM population_table
WHERE company = @company
ORDER BY population
  
OPEN population_cursor
  
SELECT @num_departments=@@cursor_rows
  
SELECT @middle=@num_departments /2
  
IF (@num_departments % 2)=0
BEGIN
FETCH ABSOLUTE @middle FROM population_cursor INTO @value1 
FETCH NEXT FROM population_cursor INTO @value2
SELECT "Company population median for " + @company + " is: " + CONVERT(char(10), (@value1 + @value2) / 2)
END
ELSE
BEGIN
SELECT @middle = @middle + 1
FETCH ABSOLUTE @middle FROM population_cursor INTO @value1 
SELECT "Company population median for " + @company + " is: " +     CONVERT(char(10), @value1)
END
CLOSE population_cursor
DEALLOCATE population_cursor
  

The find_median_population stored procedure can be executed in these ways:

EXECUTE find_median_population 'New Moon Books'
EXECUTE find_median_population 'Five Lakes Publishing'
EXECUTE find_median_population 'Scootney Books'