EXECUTE PROCEDURE

The EXECUTE PROCEDURE statement invokes a procedure.

Scope

ISQL or Application Programs

Syntax

EXECUTE PROCEDURE [:ReturnStatusVariable = ] [Owner.] ProcedureName [ ( [ ActualParameter ] [, [ ActualParameter ] ] [...] ) ]

Parameters

ReturnStatusVariable: is an integer host variable, or, for a prepared EXECUTE PROCEDURE statement, a dynamic parameter, that receives the return status from the procedure. ReturnStatusVariable can only be used when invoking a procedure from an application program, and it is always an output variable.
[Owner.]ProcedureName: specifies the owner and the name of the procedure to execute. If an owner name is not specified, the owner is assumed to be the current DBEUserID.
ActualParameter: specifies a parameter value that is passed into and/or out of the procedure. The syntax of ActualParameter is presented separately below.

SQL Syntax--ActualParameter

[ParameterName = ] ParameterValue [OUTPUT [ONLY] ]

Parameters--ParameterDeclaration

ParameterName

is the parameter name.

ParameterValue

a value that is passed into and/or out of the procedure.

For an input only parameter, the value can be any expression that does not include any aggregate function, add_months function, LONG column function, TID function, local variable, procedure parameter, or built-in variable. Column values are allowed only when the EXECUTE PROCEDURE statement is defined in a rule.

For an OUTPUT or OUTPUT ONLY parameter, the value must be a single host variable, or in a prepared EXECUTE PROCEDURE statement, a single dynamic parameter.

You can omit a parameter in calling the procedure by using a comma by itself, which is equivalent to specifying a value of NULL or the default (if one was defined when the procedure was created). However, if a ParameterName is specified, use of a comma by itself is disallowed.

OUTPUT

specifies that the caller wishes to retrieve the output value of the parameter. OUTPUT must also have been specified for the corresponding parameter in the CREATE PROCEDURE statement.

If OUTPUT is not specified, no output value is returned to the caller.

ONLY

specifies that the caller wishes to retrieve the output value of the parameter and will not provide an input value. You must also have specified ONLY for the corresponding parameter in the CREATE PROCEDURE statement. ONLY should be used, when applicable, to avoid unnecessary initialization of procedure parameters.

Description

You cannot execute a procedure from within another procedure.
If OUTPUT ONLY is not specified, a parameter that is not given a value in the EXECUTE PROCEDURE statement is assigned its default value if one was specified, or otherwise NULL if the parameter was not declared NOT NULL.
If OUTPUT ONLY is not specified, no value is provided for a parameter, a default is not specified, and NOT NULL is specified, an error is returned and the procedure is not executed.
If a procedure terminates abnormally (an error occurs in evaluating the condition in an IF or WHILE statement, or in evaluating the expression in a parameter or variable assignment), any cursors opened by the procedure (including KEEP cursors) are closed. Otherwise, except in a procedure invoked by a rule, any cursor opened by the procedure, and left open when the procedure terminates, remains open and may therefore be accessed when the procedure is executed again.
If OUTPUT has been specified for a parameter in both the CREATE PROCEDURE and EXECUTE PROCEDURE statements, any changes made to the parameter value within the procedure are returned to the calling application. The actual parameter for an output parameter can be a host variable or a dynamic parameter.
If you execute a procedure that returns multiple row result sets (contains one or more SELECT statements with no INTO clause) without using a procedure cursor, a warning is returned to the application, no result set data is returned, and any return status and output parameters are returned as usual.
You can execute procedures in ISQL, through application programs, or via rules. Further information on executing a procedure from an application is found in the ALLBASE/SQL Advanced Application Programming Guide. For the execution of procedures through rules, refer to the CREATE RULE statement.
In ISQL, you cannot specify OUTPUT for a parameter. Although return status cannot be specified in the EXECUTE PROCEDURE statement, ISQL does report the return status. Also, within ISQL, actual parameter values cannot include host variables.
If you attempt to execute a procedure that contains invalid sections, ALLBASE/SQL silently revalidates the sections. You can also use the VALIDATE statement to revalidate 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 specified 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 first 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

From an application program:

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

                                         :SalesPrice OUTPUT ONLY)

Within ISQL:

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