Input Host Variables | Teradata Vantage - Input Host Variables - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

When an SQL statement with host variable inputs is sent to the Teradata Database client for processing, Preprocessor2 extracts the input values from the corresponding host input variables and then sends them to the Teradata Database for processing.

Within stored procedures, host input variables are referred to as IN parameters. Another class of stored procedure parameter, INOUT, can be used to pass data into and out of the procedure.

Rules

  • When an input main host variable is used in an expression in a WHERE clause, or as part of a SELECT list, the data type of the host variable and the data type of the expression must be drawn from the same domain.

    You cannot specify an indicator host variable for input main host variables used in this way.

  • When you use an input main host variable to provide data for an INSERT or UPDATE, the data type of the host variable and the data type of the expression must be drawn from the same domain.

    Teradata SQL does allow mixing of character and numeric types.

    You can specify an indicator host variable for input main host variables used in this way. This data type rule does not apply to an input main host variable if an associated indicator host variable (see ) is specified and the indicator variable shows NULL.

  • If an indicator variable is specified for an input main host variable that corresponds to a non-nullable table column, then the indicator variable must always indicate not NULL.
  • Exercise care in using CHARACTER host variables as input to VARCHAR fields.

    The system strips all trailing blanks from the host value, including a single blank if that is what the host variable contains.

    For example, a host variable typed as CHARACTER(3) with a value of A ('A  ') loaded into a Teradata Database field typed as VARCHAR(10) results in the value A (‘A’) with the varying length set to 1. The two trailing pad characters are lost.

    Similarly, if the host variable has a length of 1 and the value of the field is blank, the VARCHAR field is neither blank nor null, but is a string having length 0.

    This feature differs from other systems that preserve all of the pad characters that are passed to a VARCHAR field. To preserve pad characters for a VARCHAR field, define the host variable as a VARCHAR field with length number_of_characters + number_of_pad_characters. For example, a field containing 'A  ' should be defined as VARCHAR(3) rather than CHARACTER(3).

Static Request Input Host Variables

Specify input variables used in static SQL requests by referencing the variable name where it is appropriate in the SQL statement.

The following statement is an example of a static request using an input host variable:

EXEC SQL
 SELECT field1
 FROM table1
 WHERE field2 = :var1

:var1 represents a properly defined host variable that is used to determine the rows to be selected from the table.

The application can change the value of var1 between SQL statement executions.

Dynamic Request Input Host Variables

Use input variables only with the following types of dynamic requests:
  • Those executed using an OPEN statement for a dynamic cursor
  • Those executed using an EXECUTE statement

Do not use input host variables with EXECUTE IMMEDIATE.

Input host variables in a request are represented by the question mark (?) token, referred to as a parameter marker.

When the SQL statement is executed, Preprocessor2 substitutes the appropriate host variable for the question mark (?) token in one of the following ways:
  • By use of host variable names
  • By use of an input SQLDA to describe the variables

    For example, assume that the following statement has been successfully prepared using a dynamic name of S1:

    DELETE FROM table1
    WHERE field1 = ?

    To specify the variable to be substituted for the question mark (?), the application code would contain one of the following statements:

    EXEC SQL
     EXECUTE S1 USING :var1

    or

    EXEC SQL
     EXECUTE S1 USING DESCRIPTOR INPUTDA

    where INPUTDA is a programmer-defined SQLDA structure.

    Preprocessor2 extracts the value from the host variable when the statement is executed and passes it to the Teradata Database in place of the question mark (?) parameter marker token.

Related Topics

For more information about: