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

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
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);
          *** 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.