Basic concept of Static SQL and Dynamic SQL

Static SQL:

Static SQL is the type of SQL in which we cannot change the form of SQL statements unless we change our program. The output of our program will be similar each time based upon the input value. If we use static SQL statements of UPDATE a table, then the table will be updated every time we enter new data. We cannot expect other operations from the program unless we change the whole SQL statements to make them do so.

However, the static SQL can be made flexible to some extent by using so called host variables.

This model can be useful in developing programs that are used to do same tasks time and again, like keeping record of items sold in a store (a billing system).

Dynamic SQL:

Basically, a Dynamic SQL program generates SQL statements or can take SQL statements as input, in form of string, in order to provide flexibility to the program. That is why, it can be considered to be more interactive than Static SQL. In a deeper sense, it is capable of translating the user input into an executable SQL statement. The input is capable of changing the operation performed by the program.

However, there are some statements in SQL that cannot be used dynamically.

This model is useful in programs in which we might need to use multiple kinds of operations on the same data. It is also used in such cases where we may need to use many Static SQL statements, which might prove tedious and lengthy job.  Thus, dynamic SQL provides better flexibility but still due to its complexity, it is slower than Static SQL while execution.