ALLBASE/SQL Reference Manual (36216-90216)

Chapter 4 151
Constraints, Procedures, and Rules
Using Procedures
Using Built-in Variables in Procedures
The following built-in variables can be used in error handling:
The built-in variables are read-only, and are not available outside of procedures. The first
six of these have the same meaning that they have as fields in the SQLCA in application
programs. They are always prefixed by a double colon to differentiate them from any local
variables or parameters.
Note that in procedures, sqlerrd2 returns the number of rows processed for all host
languages. However, in application programs, sqlerrd3 is used in COBOL, Fortran, and
Pascal, while sqlerrd2 is used in C.
For procedures returning multiple row result set(s), note that the built-in variables in the
procedure do not reflect the status of any FETCH or ADVANCE statements issued by the
application to manipulate a procedure cursor. After issuing such a statement, the
application should examine the appropriate fields of the SQLCA to determine status and
handle any errors.
Queries inside Procedures
Within a procedure, you can declare parameters or local variables to process either single
row or multiple row query results. Multiple row query results within a procedure must be
processed one row at a time, by means of a select cursor. A select cursor is a pointer
indicating the current row in a set of rows retrieved by a SELECT statement. Bulk
processing is not available for a select cursor within a procedure.
Multiple row query results for queries within a procedure can be processed by means of a
procedure cursor declared in a calling application. A procedure cursor is a pointer used
to indicate the current row in a set of rows retrieved by a set of SELECT statements within
a procedure. When you issue an EXECUTE PROCEDURE statement in ISQL, and the
procedure contains queries with no INTO clause, ISQL uses a procedure cursor to process
Table 4-1. Built-in Variables in Procedures
Variable Data Type Description
::sqlcode INTEGER DBERR number returned after the execution of an SQL
statement, 0 if no errors.
::sqlerrd2 INTEGER Number of rows processed in an SQL statement.
::sqlwarn0 CHAR(1) Set to “W” if an SQL warning was detected.
::sqlwarn1 CHAR(1) Set to “W” if a character string value was truncated when being
stored in a variable or parameter.
::sqlwarn2 CHAR(1) Set to “W” if a null value was eliminated from the argument set of
an aggregate function.
::sqlwarn6 CHAR(1) Set to “W” if the current transaction was rolled back.
::activexact CHAR(1) Indicates whether a transaction is in progress (“Y”) or not (“N”).
For information about transactions, see “Managing Transactions”
in the chapter “Using ALLBASE/SQL.”