I've decided that I finally really give up on stored procedures
As much as I want to play the game and use stored procedures for data access logic, time and time again I run into yet anothe rissue where I get bit. I've always pushed back on developers who want to use stored procedures for any number of reasons. There's a great list of reasons here. This time it turns out not to be the usual reasons - this time it's much simpler: there's really no way to make the whole deployment story scale in any way. As soon as you have more than one database (i.e. a multi-tenant application hitting a "private" database) you WILL run into a problem when you need to fix the always present data access bug.
Ugh... why can't I have a single place where I can put all my data access logic? Like, maybe, just maybe, a shared database holding no structure, only proc definitions? Or, maybe a DLL. Yeah, that'll do it, I'll use a DLL with dynamic SQL.