Re: "As an experiment, lets consider the opposite view to see what value it might have. If we were to remove stored procs from the database entirely in any such large application, the code would become FAR more complex and far more difficult to modify. ANY database change would require searching thru large numbers of pages to make simple SQL statement changes. Only a developer would be able to make the changes and the code would need to be recompiled for every change. "
As a further experiment, let's look at a well-designed app that uses parameterized queries. Tossing out an example of some app that uses SQL coded directly into web pages is a straw man. Any decent app has not only confined SQL to the DAL (as others have mentioned), but organized the SQL into classes for ease of maintenance. For example, in our application, you add a field to the database and add it to the associated business object. That's it. The DAL knows how to load, save, and delete. The business object knows how to enforce business rules. And recompiling isn't a bad thing. You can catch a lot of errors that way! Hacking up a stored proc in a live database looks likes a recipe for disaster to me!
Re: "Egads, I wasn't talking about having actual logins for the users of an app. I was referring to the logins that web apps run under."
If all users run under the same database login (which is usual, in my experience) then you've lost whatever fine-grained control you may have gained by using SPs. Also, who in their right mind is even giving users access to the DB? You can't touch our DB. You can only run the application. You might get a read-only database login with access to a few tables/views for ad-hoc reporting ... if you ask real nice.
Re: "Say you have a client application that issues a SQL Call. You’ve deployed it to 80 desktops. It worked fine for your first 40 users but as more users came online, it resulted in performance metrics that were problematic. You may get lucky and be able to solve the problem by adding an index, but what if the required change is really in the query itself? Maybe you are creating unanticipated locks and the query needs a NOLOCK hint. Then you have to deploy that change to the desktops. If the call were instead to a stored proc, you could optimize the SQL in that procedure and your client apps don’t know about it.
Sure, you could code up the same select statement into the ten applications that the database serves, but this strikes me as a poor reuse model as well as an opportunity to introduce defects 10 times as opposed to once."
Hmmm, I'm not buying the "do it to ease deployment issues" arguement. If you have 80 desktops, I hope you've already got a deployment methodology in place. I'm also tempted to throw in a snide remark about lack of load testing, but I do realize that bugs happen in the real world. However, I'm always cautious when people want to fix them in 5 minutes. How many "5 minute fixes" have turned into all-nighters when you broke more than you fixed?
As for 10 apps on one database, if they're sharing the data they're probably sharing some business logic as well. IMO, code-reuse goes in the application. You should be sharing business objects and a DAL, so you should still only have one change to make. Now versioning those changes among the 10 apps is another animal - one that SPs doesn't solve either.