Evil Kraig,

I do not have much time this day and the rest of the week, thus I am going to keep it short by making just points as a reply to you post.

* Performance is assisted as “not required” where clauses and even joins can be left out of dynamically build queries, based on input. Dynamic SQL is not the same as a catch all query and can be applied in many more scenario's efficiently! Trying the same from a stored procedure that builds a string to get the same effect is hard to create and maintain and inefficient process.

* Only unique instances of queries as a result from the "dynamification" process are compiled. Again, I am not talking about a catch all solution.

* Adaptive SQL is optimized SQL, you are not carrying overhead for no purpose this way.

* I indeed mean the availability of constant declarations in the when the SQL is part of the application (no more where 'IdStatus = 5' hardcoded).

* Constants are part of the logic you try to apply, be it in the application or a stored procedure. Handing over constants from the application to the procedure just so you know you talk about the same values is like having an important part of the operational logic inside the application anyway. The segregation you try to maintain simply is not there then anymore, it’s a mirage as the constants are supplied from an external source.

* Most developers know little to no SQL, because there are not told/learned to, and that is the mistake as they are perfectly capable of understanding it all. As for them not thinking in sets, i have to disagree, LINQ and such which they are eager to absorb, essentially work in sets of data too!

* Your argument why to favor a stored procedure is flawed, except for the argument about security, which i already argued is not needed for single app databases to that extend. Procedure cache optimization and injection protection, you both get when executing batches (or single statement queries) from an application with parameterization too.

* The way you write "Good SQL" as executed from an application is NOT how it is done. Purposely or not, your example code is SQL as you would write it in SQL and therefore looks cumbersome and hard to maintain. Do you have the impression that the only way to call anything database related from within an application is via a narrow stored procedure call interface?

ADO.NET for example has several ways of executing SQL, specialized around what data to expect back (scalar, sets, forward read-only reading of set).

* Opening database transactions has to happen where multiple high- or low level actions need to be grouped and succeed as one. That implies this control is always done from a higher layer in the application. For low level operations, it is perfectly reasonable that a 2nd level DAL function does this. If you do not subscribe to this, think of two independent working stored procedures that not need to succeed as one. Are you going to write a 3rd procedure doing the work of both in one transaction? Are you going to make that procedure call the other two trough exec calls? If the answer to this is yes, then good luck...and stop saying you find performance important.

* I suspect your perception of how transactions are to be controlled in an application is not how I implied it. A developer must NEVER work with objects that directly implement transaction control over a connection. The trick is not to program against the raw ADO connection, but to have a system in place that manages the "named" connection(s) and also any transaction assigned to such names for you. All a programmer needs to do is dictate when a transaction should be in place for a certain block of code and for what named connection (data source is what I like to call it). This method allows for seamless automatic nested transactions and will not get you into the issues of multiple connections operating within the same database, doing different things even when you only have one database.

* The lowest level of DAL will not need change if you are adding columns to tables that your application does not need to use.

* The lowest level of DAL is NOT hand coded, when there is change, it can be auto updated from scratch from the database schema. This means low maintenance for common changes.

* Removing columns, changing column types, removing entire tables will require some changes, but not as many as you might think. Compatibly between application logic and schema can be maintained with simple logic sometimes to ease transitions when evolving schemas. And a user interface can have its own models, tailored to its own "local" needs. This means only the interaction between this model and the DAL needs to be patched and the bulk of the code can be left intact.

* Any parameterization for a query must be send to the database, regardless what type of SQL it is you try to parameterize. There is no true distinction in call overhead here between a plain batch with parameters and a stored procedure with identical parameters.

* Your arguments about stored procedures helping troubleshooting are seen through the colored eyes of a DBA supporting an application not his own. For most common type of application this scenario simple is not reality as the team/company that develops it, also supports it. Also writing that constants are being send to the procedures as parameters, means even the stored procedures are not really in control.

* As for failures as 3AM..you are making the assumption about a lone DBA having to support applications he has no control over unless nearly all logic is in stored procedures. Quite frankly, if an application stops working that we have written it is due to a hardware/network failure and not some business logic gone mad all of a sudden. For general diagnosis of issues, we have much richer error logging then what you describe. We can see what input the user posted in a form, who the user is, what he done before this action, what the state of the session is at the time of the submit, etc. Including relevant parts of the source code in text, which is better than a stack trace.

* Do not always put yourself at the center of the application DATA universe, else you might just become that single point of failure!