Using SELECT With Dynamic SQL | SQL Fundamentals | Teradata Vantage - 17.10 - Using SELECT With Dynamic SQL - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Fundamentals

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

Unlike other executable SQL statements, SELECT returns information beyond statement responses and return codes to the requester.

DESCRIBE Statement

Because the requesting application needs to know how much (if any) data will be returned by a dynamically prepared SELECT, you must use an additional SQL statement, DESCRIBE, to make the application aware of the demographics of the data to be returned by the SELECT statement.

DESCRIBE writes this information to the SQLDA declared for the SELECT statement as follows.

THIS information … IS written to this field of SQLDA …
number of values to be returned SQLN
column name or label of nth value SQLVAR

(nth row in the SQLVAR(n) array)

column data type of nth value
column length of nth value

General Procedure

An application must use the following general procedure to set up, execute, and retrieve the results of a SELECT statement invoked as dynamic SQL.
  1. Declare a dynamic cursor for the SELECT in the form:
    DECLARE  cursor_name  CURSOR FOR  sql_statement_name
  2. Declare the SQLDA, preferably using an INCLUDE SQLDA statement.
  3. Build and PREPARE the SELECT statement.
  4. Issue a DESCRIBE statement in the form:
    DESCRIBE  sql_statement_name  INTO SQLDA

    DESCRIBE performs the following actions:
    1. Interrogate the database for the demographics of the expected results.
    2. Write the addresses of the target variables to receive those results to the SQLDA.
    This step is bypassed if any of the following occurs:
    • The request does not return any data.
    • An INTO clause was present in the PREPARE statement.
    • The statement returns known columns and the INTO clause is used on the corresponding FETCH statement.
    • The application code defines the SQLDA.
  5. Allocate storage for target variables to receive the returned data based on the demographics reported by DESCRIBE.
  6. Retrieve the result rows using the following SQL cursor control statements:
    • OPEN cursor_name
    • CLOSE cursor_name

      In this step, results tables are examined one row at a time using the selection cursor. This is because client programming languages do not support data in terms of sets, but only as individual records.

Related Information

For more information about the DESCRIBE statement, see Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148.