Deleting Rows in Result Sets

The ADO, OLE DB, and ODBC application programming interfaces (APIs) support deleting the current row on which an application is positioned in a result set. The application executes a statement, and then fetches rows from the result set. After an application has fetched the row, it can use the following functions or methods to delete the row:

• ADO applications use the Delete method of the Recordset object.
  
  
• OLE DB applications use the DeleteRows method of the IRowsetChange interface.
  
  
• ODBC applications use the SQLSetPos function with the SQL_DELETE option.
  
  
• DB-library applications use dbcursor to perform a CRS_DELETE operation.

Transact-SQL scripts, stored procedures, and triggers can use the WHERE CURRENT OF clause on a DELETE statement to delete the cursor row on which they are currently positioned, for example:

DECLARE abc CURSOR FOR
  SELECT * FROM MyTable

OPEN abc

FETCH NEXT FROM abc

DELETE MyTable WHERE CURRENT OF abc

CLOSE abc

DEALLOCATE abc