The syntax for the fetch statement is:
fetch cursor_name [into fetch_target_list]
After generating the cursor result set, Adaptive Server moves the cursor position one or more rows down the result set. It retrieves the data from each row of the result set and stores the current position, allowing additional fetches until Adaptive Server reaches the end of the result set.
For example, 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 another 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 variable (described under “Checking the cursor status”) indicating there is no more data. The cursor position remains unchanged.
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 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
Adaptive Server expects a one-to-one correspondence between the variables in the fetch_target_list and the target list expressions specified by the select_statement that defines the cursor. The datatypes of the variables or parameters must be compatible with the datatypes of the columns in the cursor result set.
