16.10 - Using SELECT With Dynamic SQL - Teradata Database

Teradata Database SQL Fundamentals
Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
klx1480972732157.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
toy1472253184295

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 SQL Stored Procedures and Embedded SQL , B035-1148 .