FETCH (Transact-SQL) - SQL Server Documentation

Description

The FETCH statement retrieves a row from a cursor. The way the row is retrieved depends on the FETCH statement's options and the cursor's scrollability.

Syntax


                        FETCH [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ] [ FROM ] cursor_name [ INTO variable_name [ ,...n ] ]

Arguments

Argument	Description
NEXT	Retrieves the next row after the current row. This is the default if no fetch option is specified.
PRIOR	Retrieves the row before the current row. This option is only supported for scrollable cursors.
FIRST	Retrieves the first row of the result set. This option is only supported for scrollable cursors.
LAST	Retrieves the last row of the result set. This option is only supported for scrollable cursors.
ABSOLUTE n	Retrieves the nth row of the result set. This option is only supported for scrollable cursors. n can be a positive or negative integer.
RELATIVE n	Retrieves the row that is n rows after (if n is positive) or before (if n is negative) the current row. This option is only supported for scrollable cursors.
cursor_name	The name of the cursor to fetch from.
INTO variable_name [ ,...n ]	Optional. Specifies that the values from the fetched row should be assigned to the specified variables. The number and data types of the variables must match the columns defined in the cursor.

Remarks

The FETCH statement is used with cursors to retrieve data row by row. The behavior of FETCH depends on the cursor's declaration, specifically its scrollability and sensitivity.

Non-scrollable cursors only support FETCH NEXT.
Scrollable cursors support fetching in both forward and backward directions, as well as absolute and relative positioning.

After a FETCH operation, the cursor's position is updated. If the fetch operation attempts to retrieve a row beyond the result set (e.g., fetching NEXT from the last row), the cursor is not advanced, and the @@FETCH_STATUS global variable is set to -1.

The @@FETCH_STATUS global variable indicates the status of the last FETCH statement executed:

0: The FETCH statement succeeded.
-1: The FETCH statement failed or the row is outside the cursor.
-2: The cursor is NULLED (e.g., when the cursor is deallocated or an error occurs).

Example

The following example demonstrates fetching data from a scrollable cursor.


                        DECLARE EmployeeCursor CURSOR SCROLL FOR
                        SELECT EmployeeID, FirstName, LastName FROM Employees

                        OPEN EmployeeCursor

                        FETCH NEXT FROM EmployeeCursor INTO @EmpID, @FirstName, @LastName

                        WHILE @@FETCH_STATUS = 0
                        BEGIN
                            PRINT 'Employee: ' + @FirstName + ' ' + @LastName

                            FETCH NEXT FROM EmployeeCursor INTO @EmpID, @FirstName, @LastName
                        END

                        CLOSE EmployeeCursor
                        DEALLOCATE EmployeeCursor