another one of the famous database holy wars, "don't use stored procedures for business logic". and at the moment HN rises to the occasion a ratio of 28 points / 60 comments.
I think at this point issues like these are pretty much baked in already. The vast majority of applications don't use stored procedures, because they are a. extremely vendor specific b. written imperatively, rather than declaratively, in vendor specific languages that let's face it are about as user friendly as REXX (Google it, kids) which make them very difficult to be expressive with c. are not very straightforward to keep versioned in source control (of course they can be versioned, but the pipeline from db environment -> source control typically has to be pretty custom, plus you have to get your DBA to use it) d. introduce all kinds of novel problems in integrating with application-level constructs such as database drivers, SQL builders, and dare I say ORMs, where while these are all potentially solvable areas (yes including ORMs), are not being solved, because the folks who swear by stored procedures pretty much despise all those other things.
So in some ways the way the stored procedure community is so opposed to application level constructs is kind of what keeps the community isolated, and in some cases, renders what might be useful technologies as completely unused (where I am referring to MySQL stored procedures, which...exist! But I wouldn't dare ever try to use them because who wants to be first, really).
MySQL stored procedures work fine when invoked from application code IME. The lack of native collection types is not ideal when you need to inject N values to a bit of data logic. As such, and for other reasons, I personally prefer raw parameterized SQL passed through a lightweight ORM that handles mapping for me as well as securely marshal a collection value into a parameterized query. But beyond that Id say that they are "usable".
Can you elaborate on the challenges you've faced with them?
for MySQL? only that they do not seem to be commonly used at all, within the already small set of modern applications that scale out on stored procedures successfully. MySQL's base of maturity is the PHP application that is using straight SQL.
I'm not an SP guy so while an SP app using a platform with lots and lots of widespread use and maturity for that style of programming, like Oracle or SQL SQL Server is already unpleasant for me but at least I'd know I was on well-trod ground, doing it for MySQL where issues I hit would have very little precedent / workarounds / community I'd not want to get involved with for anything important.
I think at this point issues like these are pretty much baked in already. The vast majority of applications don't use stored procedures, because they are a. extremely vendor specific b. written imperatively, rather than declaratively, in vendor specific languages that let's face it are about as user friendly as REXX (Google it, kids) which make them very difficult to be expressive with c. are not very straightforward to keep versioned in source control (of course they can be versioned, but the pipeline from db environment -> source control typically has to be pretty custom, plus you have to get your DBA to use it) d. introduce all kinds of novel problems in integrating with application-level constructs such as database drivers, SQL builders, and dare I say ORMs, where while these are all potentially solvable areas (yes including ORMs), are not being solved, because the folks who swear by stored procedures pretty much despise all those other things.
So in some ways the way the stored procedure community is so opposed to application level constructs is kind of what keeps the community isolated, and in some cases, renders what might be useful technologies as completely unused (where I am referring to MySQL stored procedures, which...exist! But I wouldn't dare ever try to use them because who wants to be first, really).