As the name static SQL implies, a program built using the embedded SQL features described in Chapter 17 (host variables, cursors, and the DECLARE CURSOR, OPEN, FETCH, and CLOSE statements) has a predetermined, fixed pattern of database access. For each embedded SQL statement in the program, the tables and columns referenced by that statement are determined in advance by the programmer and hard-coded into the embedded SQL statement. Input host variables provide some flexibility in static SQL, but they don’t fundamentally alter its static nature. Recall that a host variable can appear anywhere a constant is allowed in a SQL statement. You can use a host variable to alter a search condition:
exec sql select name, quota, sales
from salesreps
where quota > :cutoff_amount;
You can also use a host variable to change the data inserted or updated in a database:
exec sql update salesreps
set quota = quota + :increase
where quota >:cutoff_amount;
However, you cannot use a host variable in place of a table name or a column reference. The attempted use of the host variables which_table and which_column in these statements is illegal:
exec sql update :which_table
set :which_column = 0;
exec sql declare cursor cursor7 for
select *
from :which_table;
Even if you could use a host variable in this way (and you cannot), another problem would immediately arise. The number of columns produced by the query in the second statement would vary, depending on which table was specified by the host variable.
For the OFFICES table, the query results would have six columns; for the SALESREPS table, they would have nine columns. Furthermore, the data types of the columns would be different for the two tables. But to write a FETCH statement for the query, you must know in advance how many columns of query results there will be and their data types, because you must specify a host variable to receive each column:
exec sql fetch cursor7
into :var1, :var2, :var3;
As this discussion illustrates, if a program must be able to determine at runtime which SQL statements it will use, or which tables and columns it will reference, static SQL is inadequate for the task. Dynamic SQL overcomes these limitations.
Dynamic SQL has been supported by the IBM SQL products since their introduction, and it has been supported for many years by the minicomputer-based and UNIX-based commercial RDBMS products. However, dynamic SQL was not specified by the original ANSI/ISO SQL1 standard; the standard defined only static SQL. The absence of dynamic SQL from the SQL1 standard is ironic, given the popular notion that the standard allowed you to build front-end database tools that are portable across many different DBMS brands. In fact, such front-end tools must almost always be built using dynamic SQL.
In the absence of an ANSI/ISO standard, DB2 set the de facto standard for dynamic SQL. The other IBM databases of the day (SQL/DS and OS/2 Extended Edition) were nearly identical to DB2 in their dynamic SQL support, and most other SQL products also followed the DB2 standard. In 1992, the SQL2 standard added official support for dynamic SQL, mostly following the path set by IBM. The SQL2 standard does not require dynamic SQL support at the lowest level of compliance (Entry), but dynamic SQL support is required for products claiming the Intermediate or Full compliance levels to the SQL standard.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.