Tom,
The answer largely depends on the context. Normally you would speak of static vs dynamic SQL as applied to stored procedures, in which case static SQL refers to valid SQL constructs as per the SP syntax of the database at hand. Static statements may have parameters to be replaced with bind variables at runtime, but they are still static in the sense that their validity against the schema can be ensured at compile time.
Dynamic SQL, on the other hand, is generated by the stored procedure at runtime by way of concatenation of some sort, and as you can guess, there is no way to tell upfront whether your dynamic query will be correct SQL or not.
In essence, dynamic vs static represents the same old freedom vs safety dilemma, so it's really up to you to decide which approach fits better with the problem you are trying to solve.
Now, if we observe these concepts in the context of Java and
JDBC, they won't make much sense since there is no compile-time checking of SQL syntax. In a sense, however, we can regard prepared statements as some sort of "static" SQL, meaning that their underlying SQL strings are predetermined and won't change between invocations of execute().
Prepared statements have lots of advanteges over regular statements in terms of performance (cached execution plans), security (stronger protection from injection attacks), robustness of code (the driver takes care of proper formatting of parameters, including character escaping), etc.
As a general rule of thumb, if you need to use a query that has varying elements in it, but is still the same SQL construct if parameterized,
you should use a prepared statement. Otherwise, compose the query on the fly and be prepared to deal with all the side effects that come along with freedom