CREATE PROCEDURE

A procedure may be created through ISQL or through an application program.

A procedure result set is the set of rows returned by a procedure SELECT, FETCH, or REFETCH statement.

A select cursor (one declared for a SELECT statement) opened in an application program (i.e, outside the procedure) cannot be accessed within the procedure. However, a procedure can open and access its own select cursors.

A procedure cursor (one declared for an EXECUTE PROCEDURE statement) must be opened and accessed outside of the specified procedure, in an application program. An application can open more than one procedure cursor.

A procedure with multiple row result sets is a procedure containing one or more SELECT statements with no INTO clause. In order to retrieve one or more multiple row result sets from a procedure, you must execute the procedure using a procedure cursor. The application can then either process data from a result (by issuing the FETCH statement within the application) or advance past the result set (by issuing the ADVANCE or the CLOSE statement within the application).

If you execute a procedure without using a procedure cursor in the above case, a warning is returned to the application, no result set data is returned, and any return status and output parameters are returned as usual.

Transaction statements (COMMIT WORK, ROLLBACK WORK, WHENEVER .. STOP) executed have the usual effect on non-KEEP cursors, i.e. such cursors are closed.

A procedure executing transaction statements can close a cursor defined on itself. Therefore, transaction statements must be used with care in procedures containing statements returning multiple row result sets.

Procedures may reference the following set of built-in variables in non-SQL statements only:

The first six of these have the same meaning that they have as fields in the SQLCA in application programs. Note that in procedures, sqlerrd2 returns the number of rows processed for all host languages. However, in application programs, sqlerrd(3) is used in COBOL and Fortran, sqlerrd[3] is used in Pascal, and sqlerr[2] is used in C. ::activexact indicates whether a transaction is in progress or not. For additional information, refer to the application programming guides and to the chapter "Constraints, Procedures, and Rules."

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, or REFETCH statement with an INTO clause specifying parameters and/or local variables returns at most a one row result.

A procedure 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.

Indicator variables are not allowed or needed inside procedures. However, you can include an indicator variable with a host variable in supplying a value to a parameter in EXECUTE PROCEDURE, DECLARE CURSOR, OPEN, or CLOSE statements.

Indicator variables specified for output host variables in CLOSE, DECLARE CURSOR, or EXECUTE PROCEDURE statements are set by ALLBASE/SQL.

Syntactic errors are returned along with an indication of the location of the error inside the CREATE PROCEDURE statement.

Statements that support dynamic processing are not allowed within a procedure.

Within a procedure, a single row SELECT statement (one having an INTO clause) that returns multiple rows will assign the first row to output host variables or procedure parameters, and a warning is issued. In an application, this case would generate an error.

If the IN DBEFileSetName clause is specified, but the procedure owner does not have SECTIONSPACE authority for the specified DBEFileSet, a warning is issued and the default SECTIONSPACE DBEFileSet is used instead.