ALLBASE/SQL Reference Manual (36216-90216)

Chapter 10 343
SQL Statements A - D
CREATE PROCEDURE
• Built-in variables cannot be referenced in any SQL statement. They may be referenced
in ASSIGNMENT, IF, WHILE, RETURN, and PRINT statements. Refer to the section
"Using Procedures" in the chapter "Constraints, Procedures, and Rules" for more
explanation of built-in variables.
• Control flow and status statements, local variable declarations, parameter or local
variable assignments, and labeled statements are allowed only within procedures.
• Each
ProcedureStatement must be terminated with a semicolon.
• A label may appear only at the start of a
ProcedureStatement that is not a compound
statement, a local variable declaration, or a WHENEVER directive.
• Host variables cannot be accessed within a procedure.
• No more than 1024 result columns can be defined in a procedure result set.
• Within a procedure, any SELECT, FETCH,orREFETCH statement with an INTO clause
specifying parameters and/or local variables returns at most a one row result.
•Aprocedure with single format multiple row result sets is a procedure having
one or more multiple row result sets, whose result format is defined in the WITH
RESULT clause. Each SELECT statement with no INTO clause must return rows of a
format compatible with this defined result format. When using the WITH RESULT
clause, all such result sets in the procedure must return the same number of columns.
The corresponding result columns of each result set must be compatible in data type,
language and nullability. The corresponding result columns of each result set must be
no longer than defined in the WITH RESULT clause. (For more information about data
type compatibility, refer to chapter 7, "Data Types.")
• The WITH RESULT clause is used to describe the data format of a procedure's multiple
row result sets. Since, by definition, all single format multiple row result sets have the
same format, there is no distinction made between result sets. There is no need to issue
any ADVANCE statement in the application. Use the WITH RESULT clause only when
you do not need to know the boundary between result sets.
ALLBASE/SQL attempts to verify compatibility of each result set format with the
format defined in the WITH RESULT clause at the time the procedure is created. In
addition, since verification is not always possible at procedure creation time (sections
may be created as invalid), compatibility is also verified at procedure execution time for
each procedure result set. If incompatibility is detected during procedure creation, the
create statement returns a warning. If incompatibility is detected during procedure
execution, the execution of the procedure result set statement fails with an error, and
no more data is returned (For an ADVANCE or CLOSE, procedure execution continues with
the next statement).
• An attempt to execute a CREATE PROCEDURE statement containing a WITH RESULT
clause but no multiple row result set causes an error and the procedure is not created.
• When a procedure with single format multiple row result sets is created using the
WITH RESULT clause, the format specified in this clause is stored in the system
catalog PROCRESULT table. This format information can be returned after defining a
cursor on a procedure (at procedure execution time) with a DESCRIBE RESULT
statement
before (opening and fetching) from the cursor.