FETCH (Transact-SQL)

Retrieves rows from a cursor.

Syntax

SQL Transact-SQL

FETCH
    [ NEXT | PRIOR | FIRST | LAST | {ABSOLUTE | RELATIVE} { [ + | - ] [ expression ] } ]
    [ FROM ]
    { [ cursor_name ] | [ cursor_variable ] }
    [ INTO cursor_name | @cursor_variable ]
[ ; ]

Description

The FETCH statement retrieves the next, previous, first, last, absolute, or relative row from a cursor and makes it available for processing.

Note

The FETCH statement can only be used within a WHILE loop or another control-of-flow language statement. It can only operate on declared cursors. If you try to use FETCH on a cursor that has not been declared, an error will occur.

Parameters

Parameter	Description
`NEXT`	Retrieves the next row from the cursor and advances the cursor to the next row. This is the default value if no fetch direction is specified.
`PRIOR`	Retrieves the previous row from the cursor and moves the cursor to the previous row. This option is only supported for scrollable cursors.
`FIRST`	Retrieves the first row from the cursor and moves the cursor to the first row. This option is only supported for scrollable cursors.
`LAST`	Retrieves the last row from the cursor and moves the cursor to the last row. This option is only supported for scrollable cursors.
`ABSOLUTE [ + \| - ] [ expression ]`	Retrieves the row at the specified absolute position from the beginning of the cursor. If the expression is positive, it retrieves the row that is that many rows from the beginning. If the expression is negative, it retrieves that many rows from the end of the cursor. This option is only supported for scrollable cursors.
`RELATIVE [ + \| - ] [ expression ]`	Retrieves the row at the specified relative position from the current cursor position. If the expression is positive, it retrieves the row that is that many rows after the current row. If the expression is negative, it retrieves that many rows before the current row. This option is only supported for scrollable cursors.
`FROM cursor_name \| cursor_variable`	Specifies the cursor to be fetched from. This can be a declared cursor name or a cursor variable.
`INTO cursor_name \| @cursor_variable`	Specifies the variables into which the column values of the fetched row are to be placed. The number and data types of the variables must match the number and data types of the columns returned by the cursor.

Example

SQL Example

DECLARE @EmployeeName VARCHAR(100);
DECLARE @EmployeeID INT;

DECLARE employee_cursor CURSOR FOR
SELECT EmployeeID, Name FROM Employees WHERE Department = 'Sales';

OPEN employee_cursor;

FETCH NEXT FROM employee_cursor INTO @EmployeeID, @EmployeeName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee ID: ' + CAST(@EmployeeID AS VARCHAR) + ', Name: ' + @EmployeeName;
    FETCH NEXT FROM employee_cursor INTO @EmployeeID, @EmployeeName;
END

CLOSE employee_cursor;
DEALLOCATE employee_cursor;

Tip

The @@FETCH_STATUS global variable is crucial when working with cursors. It returns the status of the last cursor FETCH statement: 0 for success, -1 for failure or if the row is beyond the scope of the cursor, and -2 if the row is missing.