Stored procedures are remains one of a DBA’s main weaponry, there is no doubt in that, but in my opinion there are a few cases where stored procedures are a bad thing.
The first reason is that they lock you down into vendor-specific syntax which ultimately means that your system can only run on a vendor-specific RDBMS. This is fine if you are never planning on creating a shippable product, but if you do intend to create a shippable product the data layer in your product should not be dependent on a specific RDBMS. I am sure there are nuances that separate SQL Server Stored Procedure syntax from Oracle syntax, which means that if you intend to make a product work on say both SQL Server and Oracle you might have to rewrite parts of your stored procedures to be compatible with the specific vendor.
The second reason stored procedures are bad is because DBA’s try to control too much, they tend to sometimes dabble too much and exert too much control, which leads to application inflexibility. I have seen instances where the DBA returned HTML from a stored procedure, which is a total WTF moment. What happens if your data needs to be consumed in a non-web environment, like Windows Forms or WPF? What do you do with the HTML? The point is that the RDBMS is responsible for data integrity and data integrity alone, and not for returning HTML (its the role of the client consuming the data). I always had the impression that some DBAs would even run the web server using SQL Server. I have also seen DBAs place domain specific code in stored procedures. In other words they let the SQL handle business rules, which is inflexible again and it muddies the water in terms of what the code using the data must do.
The third reason stored procedures are bad is that they can become really cumbersome to maintain, in which case it makes better sense to rather have your programming language deal with data access and not have your business rules in stored procedures.
The fourth reason stored procedures are bad is because there is a preconceived notion or idea that they are more secure, which again is a load of crap. The only thing that makes it seem more secure is because the roles for a database have not been properly assigned and that you are trying to be “more secure” by adding T-SQL to apply user-specific rules. I have seen this approach fail where data gets leaked to users that are not suppose to see it because the code in the stored procedure was dodgy, not because the security was inherently bad. You can lockdown roles and their permissions in your database to a reasonable extent.
The fifth reason stored procedures are a bad thing is because they don’t really outperform ADO.NET code.