Rules For Call Arguments In BTEQ And CLIv2 - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™
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:

         CREATE PROCEDURE sp2 (OUT po1 INTEGER)
         BEGIN
           SET :po1 = 20;
         END;

    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);

    Result:

          *** 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(?);

    Result:

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

    Result:

          *** Procedure has been executed.
          *** Total elapsed time was 1 second.