Procedure Parameters

Parameters in procedure references are covered in this section when used with or in the following:
Parameters in procedure references are covered in this section when used with or in the following:
  • SQL CALL Statement
  • Query-specifications and SELECT Statements
  • WHERE Clause
Parameters in Procedure References of the SQL CALL Statement
The recommended and easiest way of specifying input parameter values is within the procedure reference itself in the SQL CALL statement. When invoked through the Command Facility, the CALL statement results in a set of output values, one for each parameter defined to the procedure. In embedded SQL a CALL statement results in an output value for each parameter specified as a host variable, a local variable or a routine parameter. For dynamically prepared CALL statements, only the parameters specified in the procedure reference of the CALL will be available as output values.
You specify parameter values supplied on a procedure reference either positionally or as keyword/value pairs. You can also combine them with WHERE clause references to form the set of values that pass to the procedure.
Examples of specifying parameter values
The example below shows different ways in which you can specify input parameter values using the SQL CALL statement.
     CALL EMP.GET_BONUS (EMP_ID = 127, START_YEAR= '1998')      CALL EMP.GET_BONUS (127, '1998')
Parameters in Procedure References in Query-specifications and SELECT Statements
The parameters associated with a procedure are treated like columns of a table. You can specify them within the column list of a SELECT or a query-specification or the search criteria of a WHERE clause. Additionally, you can specify parameter values within the procedure reference itself.
Column list references
Parameters referenced in
Column list of a SELECT statement
The columns that return to the invoking application
WHERE Clause References
You use WHERE clause references to parameters to filter the output of the procedure. Each time a procedure returns a set of output values, they are evaluated against the selection criteria specified in the WHERE clause. A non-conforming "row" results in an SQLSTATE of No Data for the initiating SQL request.
Additionally, you can use WHERE clause parameter references to pass input values to the procedure. If you specify selection criteria in the form of an "=" comparison (that is,
passes to the procedure. Other types of selection criteria such as IN predicates or comparison predicates with > or < operators have no effect on the value of the parameters passed to the procedure.
Specifically, a reference to a parameter in a WHERE clause results in an input value passing to the procedure only if:
  • It appears within an equality test
  • The equality test is not combined with other predicates in the WHERE clause through the use of the OR operator
  • The NOT operator does not precede the equality test
WHERE clause Parameter References
The examples below illustrate how a parameter reference in a WHERE clause affects the value passed to the procedure:
     WHERE clause               Parameter value                                  P1       P2        P1 = 1                     1     -null-        P1 < 1                   -null-  -null-        P1 = C1                    C1    -null-        P1 = 2 AND P2 = 3          2       3        P1 = 2 AND P2 > 3          2     -null-        P1 = 2 OR  P2 = 3        -null-  -null-        P1 IN (2, 3, 8)          -null-  -null-
Difference between procedure reference and WHERE clause
One difference exists between parameter values specified through a WHERE clause and those specified within the procedure reference. Parameter values specified within the procedure reference are not used to filter the output from the procedure as is the case for those specified within the WHERE clause. Parameter values specified within the procedure reference affect only the input to the procedure and not the output from the procedure. Therefore, the above three select statements are equivalent only if the procedure enforces the conditions specified through the procedure reference.