I'm not laboring under a misconception. I know how to do all of those things, I was doing them, it's just easier not to.
The example that comes to mind is making a change to a stored procedure's signature. In order to change it, you have to change all of the clients that talk to it at the exact same time. As a result, incremental rollouts become impossible, so you wind up doing high-risk huge-scale deployments or doing things like creating procedures named EntityInsertV2 and EntityInsertV3 or adding optional parameters with defaults or ignoring parameters that the procedure no longer needs but older clients still want to provide.
And then there's cleanup. As you really should remove EntityInsertV1 once all of the clients are using EntityInsertV2, but you can't have that in the same Update SQL, so there's really two deployments for every deployment.
If there's an easy fix for this problem (other than keeping procedural logic out of the persistence layer) I never found it.
> making a change to a stored procedure's signature. In order to change it, you have to change all of the clients that talk to it at the exact same time.
Assuming all of your clients are all server-side (ie, servlets) and are also in source control, this should not be an issue.
If you have thick clients accessing SQL functions directly, you have a bigger problem (client-server... ugh) that browsers have solved.
If you have multiple servers talking to each other, you still have a version of the "thick client" problem, namely enterprise integration... for which there are intermediary APIs.
Assuming all of your clients are all server-side (ie, servlets) and are also in source control, this should not be an issue.
Source control isn't the issue (of course everything is also in source control, this isn't the dark ages).
I'm talking about a pretty common scenario where you have one SQL server and 4 web servers acting as clients. Let's say you want to do a zero-downtime rolling deployment and upgrade the web servers one at a time while the other 3 continue to run. This is a real issue that I encountered on multiple projects that were adhering to the "everything must be in sprocs" dogma. Moving to ORMs and parameterized queries saves a lot of headaches.
This doesn't seem to have anything to do with Stored Procedures. Do a quick search & replace above:
s/stored procedure/function
... and notice that you're simply describing a common issue with building anything. If you change the interface to something, you need to change the stuff that calls it. That's just life, and it's the same story whether you have databases in the mix or not.
The distinction is that with functions, I can have my automated refactoring tools help me change them safely, I can have my compiler tell me if I get them wrong, I can have my automated tests tell me if I get them wrong.
By keeping the procedural stuff in the procedural layer and not in the persistence layer, it's easier (for me, anyway, your experience may be different) to keep things encapsulated and make contained changes in contained places.
The example that comes to mind is making a change to a stored procedure's signature. In order to change it, you have to change all of the clients that talk to it at the exact same time. As a result, incremental rollouts become impossible, so you wind up doing high-risk huge-scale deployments or doing things like creating procedures named EntityInsertV2 and EntityInsertV3 or adding optional parameters with defaults or ignoring parameters that the procedure no longer needs but older clients still want to provide.
And then there's cleanup. As you really should remove EntityInsertV1 once all of the clients are using EntityInsertV2, but you can't have that in the same Update SQL, so there's really two deployments for every deployment.
If there's an easy fix for this problem (other than keeping procedural logic out of the persistence layer) I never found it.