ALLBASE/SQL Reference Manual (36216-90216)

154 Chapter4
Constraints, Procedures, and Rules
Using Procedures
When you execute the procedure, the following is displayed:
execute procedure purchdb.partno2;
----------------+------------------------------+------------------
PARTNUMBER |PARTNAME |SALESPRICE
----------------+------------------------------+------------------
1123-P-01 |Central Processor | 500.00
1133-P-01 |Communication Processor | 200.00
1143-P-01 |Video Processor | 180.00
1153-P-01 |Graphics Processor | 220.00
---------------------------------------------------------------------
Number of rows selected is 4
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, e[nd] or n[ext] >
Entering n[ext] moves you from one SELECT statement to the next. You would see the
following:
execute procedure purchdb.partno2;
----------------+---------+---------
PARTNUMBER |BINNUMBER|QTYONHAND
----------------+---------+---------
1123-P-01 | 4003| 5
1133-P-01 | 4007| 11
1143-P-01 | 4016| 8
1153-P-01 | 4027| 5
---------------------------------------------------------------------
Number of rows selected is 4
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, e[nd] or n[ext] >
Entering n[ext] when the last result set is displayed produces a message like the following:
End of procedure result sets.
Procedure return status is 0.
isql=>
Note that although you can move back and forward through the current result set, you
cannot move back to redisplay a previous result set.
Error Handling in Procedures Not Invoked by Rules
You must provide explicit mechanisms for error handling inside procedures. The
techniques you use for this depend on whether or not the procedure is invoked by the firing
of a rule. This section describes error handling within a procedure that is
not invoked by a
rule. For information about error handling in procedures invoked by rules, see the section
“Error Handling in Procedures Invoked by Rules,” below. For information about error
handling in an application that invokes a procedure, see the section “Using Procedures in
Application Programs” in the ALLBASE/SQL Advanced Application Programming Guide.
By default, when an error occurs in an SQL statement in a procedure, the effects of the
SQL statement are undone, but the procedure continues on to the next statement. If you
want errors in SQL statements to cause an immediate error return from the procedure,
use the WHENEVER statement with the STOP option.