Coupling queries to the database (stored procedures) instead of to the code that uses them (inlined queries) seems like a recipe for deployment headaches. Especially if you're deploying new code (with new queries) every 2-4 days without downtime.
This approach only makes sense in one of two situations:
1. Ivory Tower DBAs run your company and tell developers "no" at every turn. (sad)
2. Your engineering team makes changing queries hard because they can't hire any developers who know anything about your underlying database platform internals. (also sad)
I try to be the guy who interops between the reactionary DBAs and the happy-go-lucky developers--two parties that optimize for different ends.
So most recently, we had an app that started off with direct table access via an ORM. Once the data access paths stabilized somewhat, I started replacing them with stored procedures. Those stored procedures gradually coalesced to form an API. The ORM-like functionality is still there, if need be, but the stored procedures now provide a contract, much like a service.
In retrospect, I'm not sure the ORM was even that useful. Besides encouraging certain bad habits on the consumer side (eg, most instances of lazy loading), its one more level of indirection to grapple with. Why not drop down to the database and write your implementation there? It can be tested right there and then, and directly in terms of the data flow: input -> output.
There's two huge reasons to keep procedures in the database, neither of which has anything to do with ivory towers. They're really about the most basic tenets of good coding practice:
1. Maintainability. It's necessary to put your SQL on the server if you want to keep it well factored. Just like for any other language, oft-repeated bits of SQL code should be factored out into separate procedures and functions. If you're relying on inline SQL, you're forced to choose between habitually violating the DRY principle or resorting to an unmaintainable mishmash of server-side and client-side queries.
2. Testability. The good unit testing frameworks for SQL code are written in SQL, and designed to be used from an SQL development environment (i.e., the database). And just like for any other language, your SQL code should be covered by good tests.
There are plenty of tools out there to help with deployment if it's causing difficulties for you. I recommend using them if that's what it takes for you to be comfortable with the platform.
This approach only makes sense in one of two situations:
1. Ivory Tower DBAs run your company and tell developers "no" at every turn. (sad)
2. Your engineering team makes changing queries hard because they can't hire any developers who know anything about your underlying database platform internals. (also sad)