first, next, prior, last, absolute, and relative specify the fetch direction of the scrollable cursor. If no keyword is specified, the default value is next. See the Reference Manual: Commands.
If you use fetch absolute or fetch relative, specify fetch_offset. The value can be a literal of an integer, an exact, signed numeric with a scale of 0, or a Transact-SQL local variable with an integer or numeric datatype with a scale of 0. When the cursor is positioned beyond the last row or before the first row, no data is returned and no error is raised.
When you use fetch absolute and fetch_offset is greater than or equal to 0, the offset is calculated from the position before the first row of the result set. If fetch absolute is less than 0, the offset is calculated from the position after the last row of the result set.
If you use fetch relative when fetch_offset n is greater than 0, the cursor is placed n rows after the current position; if fetch_offset n>0, the cursor is placed abs(n) rows before the current position.
For example, with the scrollable cursor stores_scrollcrsr, you can fetch any row you want. This fetch positions the cursor on the third row in the result set:
fetch absolute 3 stores_scrollcrsr stor_id stor_name --------------------------------------- 7896 Fricative Bookshop
A subsequent fetch prior operation positions the cursor on the second row of the result set:
fetch prior stores_scrollcrsr stor_id stor_name --------------------------------------- 7067 News & Brews
A subsequent fetch relative -1 positions the cursor on the first row of the result set:
fetch relative -1 stores_scrollcrsr stor_id stor_name ------------------------- 7066 Barnum's
After generating the cursor result set, in a fetch statement for a nonscrollable cursor, Adaptive Server moves the cursor position one row in the result set. It retrieves the data from the result set and stores the current position, allowing additional fetches until Adaptive Server reaches the end of the result set.
The next example illustrates a nonscrollable cursor. After declaring and opening the authors_crsr cursor, you can fetch the first row of its result set as follows:
fetch authors_crsr
au_id au_lname au_fname ----------- ------------------- --------------- 341-22-1782 Smith Meander (1 row affected)
Each subsequent fetch retrieves the next row from the cursor result set. For example:
fetch authors_crsr
au_id au_lname au_fname ----------- ------------------- --------------- 527-72-3246 Greene Morningstar (1 row affected)
After you fetch all the rows, the cursor points to the last row of the result set. If you fetch again, Adaptive Server returns a warning through the @@sqlstatus or @@fetch_status global variables (described under “Checking cursor status”), indicating that there is no more data. The cursor position remains unchanged.
If you are using nonscrollable cursors, you cannot fetch a row that has already been fetched. Close and reopen the cursor to generate the cursor result set again, and start fetching again from the beginning.
The into clause specifies that Adaptive Server returns column data into the specified variables. The fetch_target_list must consist of previously declared Transact-SQL parameters or local variables.
For example, after declaring the @name, @city, and @state variables, you can fetch rows from the pubs_crsr cursor as follows:
fetch pubs_crsr into @name, @city, @state
You can also fetch only the columns of the first row from the result set. To place the fetch columns in a list, enter:
fetch first from <cursor_name> into <fetch_target_list>
