SAVEPOINT, SAVE TRANSACTION, RELEASE [TO] SAVEPOINT

Sets a transaction save point to which a current transaction can be rolled back, resulting in a partial roll back.

Syntax:

>>---EXEC SQL--.------------.--SAVEPOINT-name--.------.-->
               +-AT db_name-+                  +UNIQUE+

 >--.-----------------------------------------------------------.---END-EXEC---><  
    +--ON ROLLBACK RETAIN CURSORS--.----------------------------+
                                   +--ON ROLLBACK RETAIN LOCKS--+

>>---EXEC SQL--.------------.--SAVE-.-TRANSACTION--.--name-- END-EXEC---><  
               +-AT db_name-+       +-TRAN---------+

>>---EXEC SQL--.------------.--RELEASE-.----.-SAVEPOINT-name---END-EXEC---><  
               +-AT db_name-+          +-TO-+

Parameter:

AT db_name

The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement.

Restrictions:

Because SQL Server does not allow save points with fire hose cursors, the following restrictions apply:

When compiling SQL Server applications that contain save points, you must specify the BEHAVIOR directive with the RO_CURSOR=FF primitive directive.
If using save points in a COBOL stored procedure with SQL Server, do not use the OPTIMIZESPCURSORS directive.

Comments:

You can define multiple save points for a single transaction.

When you set a save point using a unique name, and subsequently set another save point using the same unique name, the named save point is reset to the current transaction state.

The behavior of cursors and locks after a rollback to a save point is database-specific. For details, see the documentation provided by your database vendor.

Example:

EXEC SQL
   SAVEPOINT SP1
END-EXEC

EXEC SQL
    SAVEPOINT PHASE2 ON ROLLBACK RETAIN CURSORS
END-EXEC