ALLBASE/SQL Reference Manual (36216-90216)

ManualsBrandsHP ManualsSoftwareMPE/iX 6.0 Operating System

421

422

423

424

425

426

427

428

429

430

422 Chapter11

SQL Statements E - R

EXECUTE PROCEDURE

one was deﬁned when the procedure was created). However, if a

ParameterName

is speciﬁed, use of a comma by itself is disallowed.

OUTPUT speciﬁes that the caller wishes to retrieve the output value of the

parameter. OUTPUT must also have been speciﬁed for the corresponding

parameter in the CREATE PROCEDURE statement.

If OUTPUT is

not

speciﬁed, no output value is returned to the caller.

ONLY speciﬁes that the caller wishes to retrieve the output value of the

parameter and will not provide an input value. You must also have

speciﬁed 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 speciﬁed, a parameter that is not given a value in the

EXECUTE PROCEDURE statement is assigned its default value if one was speciﬁed, or

otherwise NULL if the parameter was not declared NOT NULL.

If OUTPUT ONLY is not speciﬁed, no value is provided for a parameter, a default is not

speciﬁed, and NOT NULL is speciﬁed, 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 speciﬁed 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 speciﬁed 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