Dynamic and Static SQL | SQL Fundamentals | VantageCloud Lake - Dynamic and Static SQL - Teradata Vantage

Dynamic SQL invokes an SQL statement by compiling and performing the statement at run time 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 term static SQL, but relational database management commonly uses that term in contrast with the ANSI-defined term dynamic SQL.

Ad Hoc and Hard-Coded Invocation of SQL Statements

Contrast dynamic SQL with ad hoc SQL statements created and run from a terminal and with preprogrammed SQL statements created by an application programmer and run 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. When 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 run.

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 must set up additional dynamic SQL statements and manipulate information in the SQLDA to make sure of a correct result.

This is done by first preparing, or compiling, an SQL text string containing placeholder tokens at run time and then running 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

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.

Related Information