Recently I discussed with a colleague when to use stored procedures. As exptected it was quite a religious conversation. A few days later I found the following screencast:
The Pros and Cons of Stored Procedures

Based on the discussion and the screencast I tried to summarize my Pros and Cons:

Pros

• Layering
• Low-level-Security
• Protect queries from changing data-access technologies (jdbc, hibernate, toplink, dbcommands, linq to sql, entity framework, nhibernate, etc…)
• Ivory-Tower-Feature: changing database during project does nearly never happen
• Coarse-grained Interface approach
• Performance
• DBA could tune the queries in a procedure
• From an integration view, logic could be reuse by several applications

Cons

• If all logic is in the database, there is a danger that the communication is too chatty (to many calls to the database-server), what is too expensive from a performance perspective
• Security-Chaos (Too much security-rules on different layers)
• If you are using the database just as an dump datastore
• Use the database for what it is strong in it (managing data, not logic)
• Developer has to maintain the logic in two different languages (java or c# and t-sql)
• Hard to test (slow, friction with current frameworks, anti-pattern for unit-tests (use of a slow infrastructure))
• Danger of duplicity of logic (in c# and in t-sql)
• Bad refactoring support
• DBA could change the logic in a procedure
• From an integration view, there could be side-effects, if a stored procedure is used by several applications and one application change the logic

Conclusion
And what is the conclusion? Surprise, surprise: It depends. You shouldn’t use stored procedures for anything and you shouldn’t categorically reject them.
I tend to use stored procedures for performance issues and try to avoid to use them to implement business logic.