Synopsis
Retrieves rows from the result set associated with an open cursor.
FETCH [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE | RELATIVE ]
[ FROM | IN ] cursor_name
[ INTO @variable_name [ ,...n ] ]Parameters
| Argument | Description |
|---|---|
NEXT | Moves the cursor forward to the next row (default). |
PRIOR | Moves the cursor backward to the previous row. |
FIRST | Moves the cursor to the first row of the result set. |
LAST | Moves the cursor to the last row of the result set. |
ABSOLUTE n | Moves the cursor to the nth row. A positive number counts from the start; a negative number counts from the end. |
RELATIVE n | Moves the cursor n rows forward (or backward if negative) from its current position. |
FROM | IN | Specifies the name of the cursor. |
INTO @variable | Stores the column values of the fetched row into the listed variables. |
Remarks
The FETCH statement must be used only after a cursor has been declared and opened. If the fetch operation cannot find a row, the variables specified in the INTO clause are set to NULL, and the @@FETCH_STATUS function returns a non‑zero value.
@@FETCH_STATUS = 0– The fetch was successful.@@FETCH_STATUS = -1– The fetch failed or the row was beyond the result set.@@FETCH_STATUS = -2– The row fetched is missing (e.g., it was deleted).
Examples
Fetching rows sequentially
DECLARE @id int, @name nvarchar(50);
DECLARE employee_cursor CURSOR FOR
SELECT EmployeeID, FirstName FROM dbo.Employees ORDER BY EmployeeID;
OPEN employee_cursor;
FETCH NEXT FROM employee_cursor INTO @id, @name;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CAST(@id AS varchar(10)) + ': ' + @name;
FETCH NEXT FROM employee_cursor INTO @id, @name;
END;
CLOSE employee_cursor;
DEALLOCATE employee_cursor;Fetching an absolute row
DECLARE @id int, @name nvarchar(50);
DECLARE emp CURSOR FOR SELECT EmployeeID, FirstName FROM dbo.Employees;
OPEN emp;
FETCH ABSOLUTE 5 FROM emp INTO @id, @name;
PRINT 'Fifth employee: ' + CAST(@id AS varchar) + ' - ' + @name;
CLOSE emp;
DEALLOCATE emp;