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

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.