Perhaps there is a better term I could be using when I referred to “dynamic SQL” in my last post. To my defense, I did mention using Prepared Statements.
The key point to keep in mind while reading the last post is that Dynamic SQL does not necessarily imply Inline SQL. By inline SQL, I mean concatenated sql statements flung all over the code like a first year classic ASP developer.
Like any good security minded developer, I detest inline SQL (as I define it here). A much better and safer approach is to use prepared parameterized SQL as Jeff Atwood outlines in this post.
So when I refer to Dynamic SQL I am referring to dynamically generated prepared parameterized SQL (that's a mouthful). These are prepared parameterized SQL statements that are generated by machine and not by hand.
As Jeff points out in this post, “Stored Procedures should be considered database assembly language: for use in only the most performance critical situations.”
Taking that abstraction one step higher, you could also consider SQL itself to be a form of database intermediate language. A dynamic SQL engine generates SQL much like a compiler takes your C# code and generates IL? When that query is executed as a prepared parameterized query, it is “jitted” by the database server into a high performance database operation.
It seems to me a decent analogy for a Dynamic SQL engine such as those built into LLBLGen Pro, NHibernate, etc...