Expansion of Procedure-reference

The expanded parameters of procedure-reference represent qualified or unqualified procedure identifiers together with an optional set of parameter values.
idmscu19
The expanded parameters of procedure-reference represent qualified or unqualified procedure identifiers together with an optional set of parameter values.
If an SQL CALL or an SQL SELECT statement that is embedded in an application program or SQL routine contains the procedure reference, then the procedure reference also identifies the target host variables, local variables, or routine parameters into which the output parameter values return.
This article describes the following information:
2
2
Syntax
Expansion of procedure-reference
►►────┬────────────────┬─ 
procedure-identifier
 ──────────────────────────────►       └─ 
schema-name
. ─┘ ►►─┬───────────────────────────────────────────────────────────────┬─────────►◄    │     ┌───────────── , ─────────────┐                           │    └─ ( ─▼── 
parameter-specification
 ──┴─ )   ─────────────────────┘
Expansion of parameter-specification
►►────┬───────────────────────┬─ 
value-expression
 ────────────────────────────►◄       └─ 
parameter-name
 ── = ─┘
Parameters
  • schema-name
    Specifies the schema with which the procedure identified by
    procedure-identifier
    is associated.
    For more information about using a schema name to qualify a procedure, see Identifying Entities in Schemas.
  • procedure-identifier
    Identifies a procedure defined in the dictionary.
  • parameter-specification
    Specifies a value assigned to a parameter of a procedure. If an SQL CALL or an SQL SELECT statement that is embedded in an application program contains the procedure and the value-expression is a host-variable, then the output value of the parameter returns into the specified host-variable. If the SQL CALL or SQL SELECT statement is embedded in an SQL routine and the value-expression is a local variable or a routine parameter then the output value of the parameter returns into the specified local variable or routine parameter.
    You can use both the positional (with NO
    parameter-name
    ) and the non-positional (with
    parameter-name
    ) forms of parameter specification in a single procedure reference. If you use a non-positional parameter specification, then all remaining parameter specifications in the parameter list MUST be non-positional. Positional parameter specifications are assumed to correspond to the declared parameters of a procedure in the sequence of their declaration.
  • parameter-name
    Specifies the name of a parameter associated with the procedure.
  • value-expression
    Specifies the input value to assign to the parameter. In addition, any host-variable, local variable, or routine parameter specified as value-expression receives the output value of the parameter returned by the invoked procedure. See Expansion of Value-expression for more information.
Usage
Referencing Procedures
You can code references to SQL procedures in an SQL CALL statement.
During SQL CALL processing, CA IDMS issues a call to the corresponding routines. The output parameter values return as a result set.
You can also reference a procedure in the FROM clause of a query-specification or SELECT statement, in the same manner as references to SQL tables, views, and table procedures.
If you reference a procedure in a FROM clause, then the parameters of the procedure act as columns in an SQL table or view. You can reference them in SELECT list expressions and WHERE clauses. A procedure returns exactly one row of output or no output.
Assigning Parameter Values with the WHERE Clause
You can use the WHERE clause as an alternative method for assigning values to parameters of procedures. An expression of the form
parameter name
=
value specification
coded in the WHERE clause is considered to be equivalent to a parameter assignment using procedure reference syntax. This allows you to code procedure references without a parenthesized parameter list, just like standard table, view or table procedures references.
This method is useful particularly if you are coding SQL statements in generic SQL environments, such as CA Visual Express, which do not support the SQL CALL statement and the specification of parameters in the procedure reference.
When you use the WHERE clause to assign parameter values, you must meet the following conditions in order to assign the parameter a value:
  • It must appear in an "=" comparison, not, for example, with >, <, >=. or <=.
  • You can combine the "=" comparison in which the parameter appears only with other factors in the WHERE clause using an AND operator. Use of an OR operator or preceding the "=" comparison with the NOT keyword means that no value is assigned to the parameter.
For more information about assignment of values to procedure parameters, see Procedure Parameters.
Examples
Qualified Procedure Reference through the CALL Statement
In the following CALL statement, the procedure reference is qualified and one parameter value is supplied as a positional parameter for the first parameter of the procedure get_bonus:
call emp.get_bonus (127);
Procedure Reference with Keyword Parameter Values
In the following CALL statement, a value is supplied for the EMP_ID parameter using keyword notation:
call get_bonus (emp_id=127);
Procedure Reference through the SELECT Statement
In the following SELECT statement, a value is supplied for the first parameter associated with the GET_BONUS procedure:
select * from get_bonus (7);
Procedure Reference through the SELECT Statement with Parameter Values Specified in the WHERE Clause
In the following SELECT statement, parameter values are supplied through the WHERE clause. This example is identical to the example above that uses keyword notation:
select * from get_bonus    where emp_id=127;
For more information about defining procedures, see CREATE PROCEDURE and Defining and Using Procedures.
 
Indicator Variables