Sybase 12.4.2 Transactions and savepoints in procedures, Some tips for writing procedures, 265

CHAPTER 6 Using Procedures and Batches

In ATOMIC compound statements, you cannot use an EXECUTE IMMEDIATE statement that causes a COMMIT, as COMMITs are not allowed in that context.

Transactions and savepoints in procedures

SQL statements in a procedure are part of the current transaction (see Chapter 8, “Transactions and Versioning”). You can call several procedures within one transaction or have several transactions in one procedure.

COMMIT and ROLLBACK are not allowed within any atomic statement (see “Atomic compound statements” on page 242).

Savepoints (see “Savepoints within transactions” on page 305) can be used within a procedure, but a ROLLBACK TO SAVEPOINT statement can never refer to a savepoint before the atomic operation started. Also, all savepoints within an atomic operation are released when the atomic operation completes.

Some tips for writing procedures

This section provides some pointers for developing procedures.

Check if you need to change the command delimiter

You do not need to change the command delimiter in DBISQL or Sybase Central when you are writing procedures. However, if you are creating and testing procedures from some other browsing tool, you may need to change the command delimiter from the semicolon to another character.

Each statement within the procedure ends with a semicolon. For some browsing applications to parse the CREATE PROCEDURE statement itself, you need the command delimiter to be something other than a semicolon.

If you are using an application that requires changing the command delimiter, a good choice is to use two semicolons as the command delimiter (;;) or a question mark (?) if the system does not permit a multicharacter delimiter.

265