 |
» |
|
|
|
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); |
|
