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

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

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 n th value SQLVAR

(n th row in the SQLVAR(n) array)
column data type of n th value
column length of n th 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
    • FETCH cursor_name USING DESCRIPTOR SQLDA
    • 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 Topics

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