Returns a row or a set of rows from a cursor result set.
fetch cursor_name [ into fetch_target_list ]
the name of the cursor
is a comma-separated list of parameters or local variables into which cursor results are placed. The parameters and variables must be declared prior to the fetch.
Returns a row of information from the cursor result set defined by the authors_crsr cursor:
fetch authors_crsr
Returns a row of information from the cursor result set defined by the pubs_crsr cursor into the variables @name, @city, and @state:
fetch pubs_crsr into @name, @city, @state
Before you can use fetch, you must declare the cursor and open it.
The cursor_name cannot be a Transact-SQL parameter or local variable.
You cannot fetch a row that has already been fetched. There is no way to backtrack through the result set, but you can close and reopen the cursor to create the cursor result set again and start from the beginning.
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.
When you set chained transaction mode, Adaptive Server implicitly begins a transaction with the fetch statement if no transaction is currently active. However, this situation occurs only when you set the close on endtran option and the cursor remains open after the end of the transaction that initially opened it, since the open statement also automatically begins a transaction.
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 indicating there is no more data, and the cursor position moves beyond the end of the result set. You can no longer update or delete from that current cursor position.
With fetch into, Adaptive Server does not advance the cursor position when an error occurs because the number of variables in the fetch_target_list does not equal the number of target list expressions specified by the query that defines the cursor. However, it does advance the cursor position, even if a compatibility error occurs between the datatypes of the variables and the datatypes of the columns in the cursor result set.
You can fetch one or more rows at a time. Use the cursor rows option of the set command to specify the number of rows to fetch.
The @@sqlstatus global variable holds status information (warning exceptions) resulting from the execution of a fetch statement. The value of @@sqlstatus is 0, 1, or 2, as shown in Table 7-24.
0 |
Indicates successful completion of the fetch statement. |
1 |
Indicates that the fetch statement resulted in an error. |
2 |
Indicates that there is no more data in the result set. This warning can occur if the current cursor position is on the last row in the result set and the client submits a fetch statement for that cursor. |
Only a fetch statement can set @@sqlstatus. Other statements have no effect on @@sqlstatus.
The @@rowcount global variable holds the number of rows returned from the cursor result set to the client up to the last fetch. In other words, it represents the total number of rows seen by the client at any one time.
Once all the rows have been read from the cursor result set, @@rowcount represents the total number of rows in the cursor results set. Each open cursor is associated with a specific @@rowcount variable, which is dropped when you close the cursor. Check @@rowcount after a fetch to get the number of rows read for the cursor specified in that fetch.
SQL92 – Compliance level: Entry-level compliant.
The use of variables in a target list and fetch of multiple rows are Transact-SQL extensions.
fetch permission defaults to all users.
Commands declare cursor, open, set
