Relational Syntax for Calling Virtual Procedures

Procedural relational commands use the syntax of a SELECT command to emulate an EXEC. In a procedural relational command, procedure group names are used in a FROM clause in place of a table. This procedure will be executed in place of a normal table access if all of the necessary input values can be found in the criteria against the procedure. Each combination of input values found in the criteria results in an execution of the procedure.

Syntax

SQL

SELECT * FROM proc
-- or
SELECT output_param1, output_param2 FROM proc WHERE input_param1 = 'x'
-- or
SELECT output_param1, output_param2 FROM proc, table WHERE input_param1 = table.col1 AND input_param2 = table.col2

Syntax Rules

The procedure as a table projects the same columns as an EXEC with the addition of the input parameters. For procedures that do not return a result set, IN_OUT columns will be projected as two columns, one that represents the output value and one named {column name}_IN that represents the input of the parameter;
Input values are passed via criteria. Values can be passed by '=','is null', or 'in' predicates. Disjuncts are not allowed. It is also not possible to pass the value of a non-comparable column through an equality predicate;
The procedure view automatically has an access pattern on its IN and IN_OUT parameters which allows it to be planned correctly as a dependent join when necessary or fail when sufficient criteria cannot be found;
Procedures containing duplicate names between the parameters (IN, IN_OUT, OUT, RETURN) and result set columns cannot be used in a procedural relational command;
Default values for IN, IN_OUT parameters are not used if there is no criteria present for a given input. Default values are only valid for named procedure syntax.

The usage of IN or JOIN criteria can result in the procedure being executed multiple times.

None of the issues listed in the syntax rules above exist if a nested table reference is used.