My, how different the worlds that we live in are !
I am sure that Pat's definition is correct in a context that is unknown to me, but in the Sybase world:
"Dynamic SQL" is what happens when you send a batch of SQL to the server. The server parses it, builds a query plan (identifies and resolves referenced objects) for it, builds a query tree (identifies resources) for it, allocates resources for it, executes it, and sends back the result set.
"Static SQL" is what happens when you put the [possibly same] SQL into a stored procedure and compile it (once). The server parses it, builds a query plan for it, builds a query tree for it (once). Then every time you execute the stored proc, it grabs the query plan (it may already be in memory for another user), allocates resources, executes it and returns the result set. Much faster because the required objects are known, parsing is not required, etc, and yes, changes to the objects referenced in the stored proc force it to be recompiled. Dynamic VALUES (known only at runtime) are passed as parameters to the static query, and this is quite ordinary. Dynamic SQL also has various limitations (eg. Exec(); no of verbs; error checking and passing back, to name just a few) which Static SQL does not have, but most important, it is hideous re transaction control.
One characteristic of a 'poor' application is that is stores the SQL in the client-side program, and therefore is always running Dynamic SQL, with the above overheads. Further, such SQL may not always work (eg. due to values out-of-range, programming errors, etc). In this regard a 'good' application has all its SQL stored on the server (well once that particular release of the app has been developed, the SQL does not change), and the SQL always works. Server objects can be monitored and administered with a lot more ease than trying to catch a bunch of SQL executing on the fly.
Some would say that SELECTs do not matter: to an extent that is true, but 'really good' apps (in this one regard) compile everything (why would you want to run at half the speed ?!?). (Of course, third party reporting tools cannot help but do Dynamic SELECTs).
BTW (and I am not addressing what is clearly an example above) anyone actually implementing SELECT * FROM <table> or INSERT ... without a column list should change careers. Email me if you do not know why.