Oracle cursors can move in a forward direction only–there is no backward or relative scrolling capability. SQL Server cursors can scroll forward and backward with the fetch options shown in the following table. These fetch options can be used only when the cursor is declared with the SCROLL option.
| Scroll Option | Description |
|---|---|
| NEXT | Returns the first row of the result set if this is the first fetch against the cursor; otherwise, it moves the cursor one row within the result set. NEXT is the primary method used to move through a result set. NEXT is the default cursor fetch. |
| PRIOR | Returns the previous row within the result set. |
| FIRST | Moves the cursor to the first row within the result set and returns the first row. |
| LAST | Moves the cursor to the last row within the result set and returns the last row. |
| ABSOLUTE n | Returns the nth row within the result set. If n is a negative value, the returned row is the nth row counting backward from the last row of the result set. |
| RELATIVE n | Returns the nth row after the currently fetched row. If n is a negative value, the returned row is the nth row counting backward from the relative position of the cursor. |
The Transact-SQL FETCH statement does not require the INTO clause. If return variables are not specified, the row is automatically returned to the client as a single-row result set. However, if your procedure must get the rows to the client, a noncursor SELECT statement is much more efficient.
The @@FETCH_STATUS function is updated following each FETCH. It is similar in use to the CURSOR_NAME%FOUND and CURSOR_NAME%NOTFOUND variables used in PL/SQL. The @@FETCH_STATUS function is set to the value of 0 following a successful fetch. If the fetch tries to read beyond the end of the cursor, a value of –1 is returned. If the requested row has been deleted from the table after the cursor was opened, the @@FETCH_STATUS function returns –2. The value of –2 usually occurs only in a cursor declared with the SCROLL option. This variable must be checked following each fetch to ensure the validity of the data.
SQL Server does not support Oracle’s cursor FOR loop syntax.