The principle difference between static and dynamic SQL statements is the point in time when the SQL statements are constructed and compiled.
A static SQL statement is embedded in a host language program and must be precompiled. The type of statement, the tables, views, columns referenced, and search conditions cannot be changed at runtime. Although host variables can be used to provide search values at runtime, the general content of the statement, for example, the derived column list, the search conditions, cannot be changed at runtime.
A dynamic SQL statement is constructed at runtime. The statement, including the tables, views and columns referenced, and the search conditions, is compiled at runtime.
Dynamic SQL usage requires the use of the following statements:
PREPARE, EXECUTE, EXECUTE IMMEDIATE and DESCRIBE.
There are also some SQL statements which are normally used as static SQL statements, but have an extended functionality for dynamic SQL:
DECLARE CURSOR, OPEN and FETCH.
Note: The above statements are embedded in the application program like any other static SQL statement. However, they enable the use of dynamic SQL statements which are not embedded in the application program.
There are various methods of using dynamic SQL statements, mainly depending on the type of SQL statement (SELECT or NON-SELECT statements) and the degree of flexibility required.