15.00 - Dynamic and Static SQL - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Dynamic and Static SQL

Dynamic SQL is a method of invoking an SQL statement by compiling and performing it at runtime from within an embedded SQL application program or a stored procedure. The specification of data to be manipulated by the statement is also determined at runtime. Static SQL is, by default, any method of invoking an SQL statement that is not dynamic.

ANSI Compliance

Dynamic SQL is ANSI/ISO SQL:2011-compliant. The ANSI/ISO SQL standard does not define the expression static SQL, but relational database management commonly uses it to contrast with the ANSI-defined expression dynamic SQL.

Ad Hoc and Hard-Coded Invocation of SQL Statements

Perhaps the best way to think of dynamic SQL is to contrast it with ad hoc SQL statements created and executed from a terminal and with preprogrammed SQL statements created by an application programmer and executed by an application program.

In the case of the ad hoc query, everything legal is available to the requester: choice of SQL statements and clauses, variables and their names, databases, tables, and columns to manipulate, and literals.

In the case of the application programmer, the choices are made in advance and hard-coded into the source code of the application. Once the program is compiled, nothing can be changed short of editing and recompiling the application.

Dynamic Invocation of SQL Statements

Dynamic SQL offers a compromise between the extremes of ad hoc and hard-coded queries. By choosing to code dynamic SQL statements in the application, the programmer has the flexibility to allow an end user to select not only the variables to be manipulated at run time, but also the SQL statement to be executed.

As you might expect, the flexibility that dynamic SQL offers a user is offset by more work and increased attention to detail on the part of the application programmer, who needs to set up additional dynamic SQL statements and manipulate information in the SQLDA to ensure a correct result.

This is done by first preparing, or compiling, an SQL text string containing placeholder tokens at run time and then executing the prepared statement, allowing the application to prompt the user for values to be substituted for the placeholders.

SQL Statements to Set Up and Invoke Dynamic SQL

The embedded SQL statements for preparing and executing an SQL statement dynamically are:

  • PREPARE
  • EXECUTE
  • EXECUTE IMMEDIATE
  • EXECUTE IMMEDIATE is a special form that combines PREPARE and EXECUTE into one statement. EXECUTE IMMEDIATE can only be used in the case where there are no input host variables.

    For details, see SQL Stored Procedures and Embedded SQL.

    Related Topics

     

    For more information on …

    See …

    examples of dynamic SQL code in C, COBOL, and PL/I

    Teradata Preprocessor2 for Embedded SQL Programmer Guide.

    embedded SQL statements

    SQL Stored Procedures and Embedded SQL.