ALLBASE/SQL Reference Manual (36216-90216)

Chapter 11 423

SQL Statements E - R

EXECUTE PROCEDURE

invalid sections in procedures.

• You can PREPARE and EXECUTE an EXECUTE PROCEDURE statement containing

dynamic parameters.

You can use EXECUTE PROCEDURE inside an EXECUTE IMMEDIATE statement, provided

the EXECUTE PROCEDURE statement includes neither dynamic parameters nor host

variables.

• If you do not specify OUTPUT for a parameter declared as OUTPUT in the CREATE

PROCEDURE statement, no value is returned.

• You cannot specify OUTPUT for a parameter not declared as OUTPUT in the CREATE

PROCEDURE statement.

• OUTPUT ONLY must be speciﬁed for any parameter declared as OUTPUT ONLY in

the CREATE PROCEDURE statement if an actual parameter is provided. Use of OUTPUT

ONLY improves performance, since no time is spent initializing the parameter to the

input value, default value, or null.

• Within a procedure, a single row SELECT statement (one having an INTO clause) that

returns multiple rows will assign the ﬁrst row to output parameters or local variables,

and a warning is issued. In an application, this case would generate an error.

Authorization

You must have OWNER or EXECUTE authority for the procedure or DBA authority to use

this statement.

Examples

1. From an application program:

EXECUTE PROCEDURE :Status = Process12(:PartName, :Quantity,

:SalesPrice OUTPUT ONLY)

2. Within ISQL:

isql=> execute procedure Process12('Widget',150);