I'm working at a project in one of the world's top 3 IT consulting firms, and was told by a DBA that company best practice's state stored procedures are not a "best practice". This is so contrary to everything I've learned.
Stored procedures give you code reuse, and encapsulation (two pillars of software development), security (you can grant/revoke permissions on an individual stored proc), protect you from SQL injection attacks, and also help with speed (although that DBA said that starting with SQL Server 2008 that even regular SQL queries are compiled if they are run enough times).
We're developing a complex app using Agile software development methodology. Can anyone think of good reasons why they wouldn't want to use stored procs? My guess was that the DBAs didn't want to maintain those stored procs, but there seem to be way too many negatives to justify such a design decision.
Best Answer
In my experience working on very large projects, you have to be very clear on where business logic lives. If you allow an environment where individual developers can put business logic in the business object layer or in a stored procedure as they see fit, a large application becomes VERY difficult to understand and maintain.
Stored procedures are great for speeding up certain DB operations. My architectural decision is to leave all logic in the business layer of the application and employ stored procedures in a targeted manner to improve performance where benchmarking indicates it is warranted.