17.05 - Rules For Specifying Input And Output Parameters - 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)

Call arguments consisting of input and output parameters must be submitted with a CALL statement. No default parameter values can be defined at the time a procedure is created; the CALL returns an error if the required call arguments are not specified.

The following rules apply to the parameter arguments:
  • The number of call arguments in a CALL statement must be equal to the number of parameters in the called procedure.

    If the called procedure has no parameters, you cannot specify any call arguments.

  • An IN, INOUT or OUT argument must correspond to an IN, INOUT or OUT parameter respectively in the called procedure.
  • The default data type for an INOUT parameter input value is determined by the narrowest type that can contain the data passed to the INOUT parameter at run time, not by the default type defined for the parameter in the CREATE PROCEDURE (External or SQL Form) statement that created the procedure.
    To ensure that memory overflow errors do not occur when the size of an output value passed to an INOUT parameter exceeds the capacity of the default data type derived from the input value for that parameter, you must take either of the following precautions:
    • Explicitly cast the INOUT parameter data type in the CALL statement to ensure that memory overflow errors do not occur.

      For example, if you submit the following CALL statement, the system determines that the smallest data type that can contain the input value to the INOUT parameter is SMALLINT, which requires 2 bytes, so it assigns the SMALLINT type to the parameter irrespective of the data type assigned to it at the time the procedure was created.

           CALL my_proc(32767);

      However, if the call returns a value greater than or equal to 32,768 to the INOUT parameter, the statement aborts unless you cast its type to the type that was assigned to the parameter at the time the procedure was created, or at minimum to INTEGER. The INTEGER type can contain positive values as large as 2,147,483,647, the BIGINT type can contain positive values as large as 9,223,372,036,854,775,807, the DECIMAL/NUMERIC type can contain far larger numbers, and so on. For details, see SQL Data Types and Literals and Database Design . For example, you can rewrite the statement as follows:

           CALL my_proc (CAST('32767' AS INTEGER));
    • Code the calling application to ensure the appropriate type conversion is made. For a JDBC example, see example 4 in Example: Preventing Memory Overflow Errors for INOUT Parameters.

      For details, see CREATE PROCEDURE (External Form) or CREATE PROCEDURE (SQL Form) in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

      You must ensure either that the default data type for an INOUT parameter can accommodate the value or to know what type to cast it to in order to ensure that a memory overflow error does not occur. For information about how to determine the size of a literal, see Teradata Vantage™ - Data Types and Literals, B035-1143.

  • Some additional rules and restrictions apply when CALL is submitted from various client utilities. See the following for the specific rules and restrictions:
  • IN parameters can only have input, and OUT parameters can only have output. INOUT parameters can have both input and output.
  • The data type of a call argument must be compatible with the data type of the corresponding parameter in the called procedure.
  • A value expression of NULL can be used to assign NULL to the corresponding IN or INOUT parameter in the called procedure.
  • On successful completion of the procedure execution, the ACTIVITY_COUNT in the success response is set to the following values:
ACTIVITY_COUNT Setting Procedure Contains
1 Output (INOUT or OUT) parameters.
0 No output parameters.