Dynamic SQL Is Not Inline SQL
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…