Using the where current of Parameter

Use the clause where current of with cursors.

Before deleting rows using the clause where current of, first define the cursor with declare cursor and open it using the open statement. Use one or more fetch statements to position the cursor on the row to delete. The cursor name cannot be a Transact-SQL parameter or local variable. The cursor must be an updatable cursor, or the SAP ASE server returns an error. Any deletion to the cursor result set also affects the base table row from which the cursor row is derived. You can delete only one row at a time using the cursor.
You cannot delete rows in a cursor result set if the cursor’s select statement contains a join clause, even though the cursor is considered updatable. The table_name or view_name specified with a delete...where current of must be the table or view specified in the first from clause of the select statement that defines the cursor.
After the deletion of a row from the cursor’s result set, the cursor is positioned before the next row in the cursor’s result set. You must issue a fetch to access the next row. If the deleted row is the last row of the cursor result set, the cursor is positioned after the last row of the result set. The following describes the position and behavior of open cursors affected by a delete:
- If a client deletes a row (using another cursor or a regular delete) and that row represents the current cursor position of other opened cursors owned by the same client, the position of each affected cursor is implicitly set to precede the next available row. However, one client cannot delete a row representing the current cursor position of another client’s cursor.
- If a client deletes a row that represents the current cursor position of another cursor defined by a join operation and owned by the same client, the SAP ASE server accepts the delete statement. However, it implicitly closes the cursor defined by the join.