"That just sounds like bad coding, pure and simple. Hundreds of thousands of queries? How could that ever be a good idea?"
From the VB programmer's point of view it seemed like a good idea. The purpose of the application was to determine the eligibility of thousands of people for health care benefits based on a complex set of rules based on number of hours worked, hire date, where they worked, when they worked, and other personal information. The programmer executed a series of queries to get the necessary information for one worker for one time period. They then used that information to calculate whether or not that person had earned eligibility and executed an update query to save the eligibility information. They put that block of logic inside 2 loops - one to iterate through all of the workers and one to iterate through all of the time periods for each worker. To the VB programmer that made perfect sense - it was just like using arrays. To a database programmer that is insane, but to a VB programmer it made perfect sense.
"An illusion, as we have had table changes which broke dozens of stored procedures. It's the same problem, just on a different (and harder to debug) tier. Reality is, changing the DB will break everyone, which is why I favor total transparency whenever possible."
You seem to have missed my point. If all of the code referencing a table is contained in stored procedures I can find all of the affected stored procedures by doing one query against the syscomments table (where all stored procedure source code is stored by SQL Server). I do not know of any way that I can find all of the VB code that may exist that references a table because there is not a single known place where all of the source code resides. The issue is not how many pieces of code will be affected, the issue is how do I find all of the affected pieces of code?
"Stored procedures can be designed, written, and tested separately from the VB code.
So can API layers. Stored procs are not the only form of database abstraction, just one of the more limited ones."
Once again I think you missed my point. Whether you execute queries directly from VB or you put them into stored procedures the queries themselves must be tested. I believe that it is better (and easier) to write and test stored procedures separately so that when you are testing and debugging your VB code you have eliminated the dynamic SQL as a possible source of problems.
"It's a lot easier to hide a bad database design by sticking it behind a bunch of stored proc "interfaces". The DB schema should ALWAYS be well designed, and the more eyes you have on it, the more likely it is to be well designed. If 4-5 developers can't figure out the crazy queries necessary to do basic business work in your schema, maybe it isn't very good?"
In discussing the pros and cons of using stored procedures vs. dynamic queries from VB we should both be assuming the presence of skilled programmers on both the VB side and the database side. It is somewhat disingenuous to argue that you need to put the queries in the VB code because, if you don't, the database people will write a bunch of crappy code and stick it in stored procedures that you can't understand and are stuck with. It would be equally wrong for me to argue that VB programmers should be forced to use stored procedures because if they are allowed to write queries against the database they will be very inefficient and bog down the database server or write update queries that screw-up the data. Even though that may be true given the talent levels on your projects, the philosophical argument should assume that both sides have talented people.
The question is, if given equally talented people on both sides, what is the best way to work with a database and why?