RECAP ON OTHER ANSWERS
Here is a quick summary of what others have said here before.
Pro company policy:
- Allows for tracking dependencies between database objects and overview how planned changes affect the schema;
- DBA team is able to review and control application access rights;
- DBA team is able to predict performance impact of changes;
- Decouples database from application code;
- Broken window theory... meaning that's the way how they organize their code, and breaking the consistency will makes the infrastructure harder to grasp for newcomers, which will make them respect and strive for quality less;
- You get your paycheck to do what you were asked to do. This is a company policy and right now you are not in the authority to change it, so better live with it.
Contra company policy:
- This company mindset is very rigid and dogmatic, and the policy makes developer's work unnecessarily cumbersome. Unfortunately, see last point in the Pros section;
- As back2dos refers to it by saying "For every query made to the database, you have to look up the stored procedure", a sprocs-only policy often results in code duplication, because different developers have no idea which sprocs could be reused for their problem at hand;
- Also, ironically the contrary to the previous case creates problems too, when some application owns a sproc, then another reuses it, then the first one updates it without the knowledge of who else started relying on it. DBAs track the dependencies not further than the walls of the DB server room, so don't expect them to give a damn what app relies on what outside of that. If the application teams don't track it between themselves that's their problem, the DBAs are covered.
THOSE 2 CENTS
Firstly, many answers here use the word standard. The practice of prohibiting direct queries and only allowing sprocs is not called a standard. It's a policy (see jzd's answer).
Secondly, specific to your problem: my main contra argument against such a restrictive policy of using stored procedures exclusively would be the SQL language itself, and not necessarily the centralized business-logic repository infrastructure it promotes (though that has it's counter-arguments too).
SQL is a rather rigid and non-composable language, with quite limited expressive power. This means that you will hit a dead-end very early with regards to code reusability. One of the reasons of this rigidity is that there are no means to pass first class functions in any way (like with OOP languages using polymorphism), which limits composability significantly. The closest you can get to that in expressive power is by writing dynamic SQL queries constructed using string concatenation. Not a neat thing. Dynamic queries defeat some of the points in the "pros" section, like the tracking dependencies between DB objects, and usually have worse performance, error prone, hard to debug, and increase the risk of SQL injection attacks. Unfortunately, with SQL you'll find that you can't get very far with extracting common reusable logic between sprocs without hitting the wall and being forced to resort to dynamically executed queries.
UPDATE: Another big limitation of stored procedures, besides the first class function thing, is the passing and returning of composite data types as arguments, whether it be lists, sets, records, or key-value pairs. This hurts composability badly too.
Finally, I don't necessarily agree with one of the pro points above, the "decoupling DB from application" by Jorge: The main principle which I feel applies here is to prefer primitive data structures with large set of common reusable and composable operations, rather than working with custom APIs. Sprocs are such custom APIs here, which stand in-between the user and the primitive relational data to query it using common composable data manipulation primitives (select, join, where, group by, etc). Now SQL itself is not the ideal choice to be the DSL for composable data manipulation primitives, due to the above mentioned rigidity, but with a more sensible language choice (like .NET Linq... or Lisp/Clojure!) you could run your logic against a simple List just the same way as against a DB ResultSet. Obviously, that makes it easily testable, which is a Good Thing. I say prefer your data-store to be dumb simple and primitive, such that it can be stubbed with plain CSVs. As you see, this model decouples DB from application too, only it draws the line at a lower level of abstraction.
WHAT TO DO NEXT?
It's a bit unrelated to the question, but I encourage you to take a look at Datomic, which has an interestingly novel approach to storing and querying data, in line with some of the above observations. (Obviously, I mean look at it strictly outside the work environment first... definitely do NOT go to the CTO's office the next day and say "Hey guys I rewrote some of your sprocs in Datomic and deployed it on this shiny prod server over there, it's really cool take a look!" They might not appreciate the otherwise completely understandable excitement ;)