Deleting Rows in Result Sets

The ADO, OLE DB, and ODBC 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 currently are positioned. The following example deletes a single row from the EmployeePayHistory table using a cursor named complex_cursor. DELETE affects only the single row currently fetched from the cursor.

USE AdventureWorks2008R2;
DECLARE complex_cursor CURSOR FOR
    SELECT a.BusinessEntityID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.BusinessEntityID = b.BusinessEntityID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
DELETE FROM HumanResources.EmployeePayHistory
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;