Both static and dynamic SQL statements are allowed in the embedded SQL host program. Static SQL statements, as the name implies, remain static each time the program is run. Dynamic SQL statements are built at runtime.
To use static SQL statements in a host program, the SQL statement type, and the database table and column names are required. Only the specific data values the statement is looking for are unknown: use host language variables to represent those unknown values. For example, use a static SQL statement to enter an order, and a host language variable to represent the quantity of an item in the order.
Static SQL statements are hard‑coded into the program, therefore the statements require parsing, and so forth, only once. Using static SQL results in faster processing.
Dynamic SQL is useful for programs in which the content of a SQL statement is unknown at the time the program is written. For example, an interactive application that prompts the user for a table name would be a good situation in which to use dynamic SQL.
Each host programming language handles dynamic SQL statements differently. Refer to Chapter 3: “Writing Embedded SQL Applications in C”, Chapter 4: “Writing Embedded SQL Applications in COBOL”, Chapter 5: “Writing Embedded SQL Applications in PL/I”, and Appendix C: “Embedded SQL Examples” for more information.