17.05 - Rules For Call Arguments In BTEQ And CLIv2 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Advanced SQL Engine
Teradata Database
Release Number
January 2021
English (United States)
Last Update
These rules apply to call arguments submitted from applications in BTEQ or CLIv2:
  • An IN or INOUT argument must be a value expression.
  • In a value expression used as IN or INOUT argument, identifiers prefixed by the colon (:), if any, must refer to USING variables associated with a USING clause for the statement containing the CALL. The value of the expression is treated as the input value for the corresponding parameter in the called procedure.
  • An OUT argument can be any of the following:
    • An output host variable.
    • A place holder such as a QUESTION MARK character.
    • Any name other than the OUT parameter name specified in the procedure definition.
  • These rules apply to using ? parameters.

    The following procedure call returns AMBIGUOUS, AMBIGUOUS as the information for the parameters p1 and resutl_1 with the expectation that during the execution phase, additional information will be forthcoming about the type of the parameter p1, which will determine the resulting type of result_1 parameter.

         CALL xsp2 (?, ?);

    This following procedure call fails because there is no expectation that there will be additional information about what data type the result_1 parameter should return.

         CALL xsp1 (?, ?);

    These examples show variations in the specification of OUT parameter names in a CALL statement issued from BTEQ. They s are based on the following CREATE PROCEDURE statement:

           SET :po1 = 20;

    In the following CALL statement, the OUT argument name p1 differs from the OUT parameter name po1 specified in the procedure definition:

         CALL sp2(p1);
          *** Procedure has been executed.
          *** Total elapsed time was 1 second.

    The same CALL statement also works if you specify a placeholder character or host variable instead of the explicit parameter specification, as the two following examples show:

         CALL sp2(?);
          *** Procedure has been executed.
          *** Total elapsed time was 1 second.
         CALL sp2(:tx1);
          *** Procedure has been executed.
          *** Total elapsed time was 1 second.