ALLBASE/SQL Reference Manual (36216-90216)

156 Chapter4
Constraints, Procedures, and Rules
Using Procedures
the RETURN statement causes an immediate return from the procedure following the error.
Following the return from a procedure, an application program can retrieve the messages
from raised errors by using the SQLEXPLAIN statement. Since SQLCODE is 0 in this case
(because the procedure executed correctly; it was an SQL statement within it that received
the error), you should execute SQLEXPLAIN in a loop that tests SQLWARN[0], as follows:
while (sqlwarn[0]=='W')
EXEC SQL SQLEXPLAIN :SQLMessage;
However, SQLEXPLAIN cannot be used within the procedure itself. You should document the
cause of all errors generated by the RAISE ERROR statement in a procedure so that the
procedure caller can understand the error condition.
NOTE The behavior of errors, including RAISE ERROR, in procedures called by rules
differs somewhat from that described here. Refer to “Using RAISE ERROR in
Procedures Invoked by Rules” for more information.
Recommended Coding Practices for Procedures
The use of procedures can have indirect consequences that the procedure writer and the
procedure caller may not anticipate. Problems are most likely to arise in the areas of
transaction management, cursor management, error handling, and DBEnvironment
settings. In order to minimize difficulty, good communication between the procedure writer
and the caller of the procedure is essential. Thus procedures should be carefully
documented as to what is expected from the calling application, and applications should be
carefully documented as to what they expect a called procedure to do and not to do.
Within a procedure, you can use ISQL comments or comment notation for the
programming language of an application that invokes a procedure. See the
ALLBASE/ISQL Reference Manual or the appropriate ALLBASE/SQL application
programming guide for information about comments.
The following practices are suggested to ensure that a procedure is always called under the
same conditions and with the same expectations:
• If the procedure might execute a COMMIT or ROLLBACK, the application should issue a
COMMIT or ROLLBACK before calling the procedure. Any cursors opened in the
application with the KEEP cursor option and subsequently committed should be closed
and committed before the application calls the procedure.
• Documentation of the calling application should clearly state the following:
— Whether the procedure will be called with a transaction open.
— Whether the procedure is expected to have COMMIT or ROLLBACK statements.
— Whether the procedure is expected to be atomic.
The following practices are suggested to ensure that a procedure will always execute as
expected:
• Procedure execution should not span transaction boundaries. Either the procedure
should be treated as an atomic transaction, that is, it should always issue a COMMIT or