Well, stored procedures have some limitations. For example, one cannot use variables as universally as one might like, altough they may have lifted some of these restrictions for MySQL since last time I checked. They also cannot parametrize variations of the statement itself nicely, or help break up very large statements into smaller pieces.

Which is of course to be expected for constructed statements, like for search functionality. If it were only for a single search function, it might be okay to make an exception, but otherwise you might end up having two very different places to look for SQL code. Btw, they're also useless for statements that aren't about data manipulation. So either you use a very simplistic mechanism to establish your schema, or you will need to add a way to execute non-stored statements anyways, in the end doubling the code required to interact with the dbms process.

By the way, stored procedures aren't as popular as one might think, which means that there are many bindings for SQL apis that don't support them at all! And, last but not least, if you code in ML, you might want the compiler to type check your SQL statements as well, which makes not much sense if it is optional, as one feature of stored procedures is that you may change them without rebuilding your application...

So, while I like the idea a lot, I have seriously regretted each and every attempt to use them in my own projects, and I have seen lots of code whose only purpose was to work around their limitations.