Syed Ali discussed stored procedures and why use them. I posted my comments there but thought it was worthy of an entry in the blog. There are some things I hate about them as well but I feel they have some very distinct advantages too so they are a necessary evil. I don't see how you can argue against stored procedures. There are drawbacks just as there is with any technology but the advantages outweigh the disadvantages.
Advantages
Advantage 1: Stored procedures are modular. This is a good thing from a maintenance standpoint. When query trouble arises in your application, you would likely agree that it is much easier to troubleshoot a stored procedure than an embedded query buried within many lines of GUI code.
Advantage 2: Stored procedures are tunable. By having procedures that handle the database work for your interface, you eliminate the need to modify the GUI source code to improve a query's performance. Changes can be made to the stored procedures--in terms of join methods, differing tables, etc.--that are transparent to the front-end interface.
Advantage 3: Client execution requests are more efficient. For example, if an application needs to INSERT a large binary value into an image data column not using a stored procedure, it must convert the binary value to a character string (which doubles its size), and send it to SQL Server. When SQL Server receives it, it then must convert the character value back to the binary format. This is a lot of wasted overhead. A stored procedure eliminates this issue as parameter values stay in the binary format all the way from the application to SQL Server, reducing overhead and boosting performance.
Advantage 4: Cached execution plans that boost performance. You can't get performance out of sql buried inside your source code. Any code that is sent over the wire will be smaller when executed as a stored procedure than sending it's content. So it is also true that a stored procedure will understand the path through the stored proc before execution and cache that for better performance.
Advantage 5: Centralized security, administration, and maintenance. There is nothing currently in place that can rival the tools built for the database in this area. Nothing... It just doesn't exist.
Now... As a developer I can understand the desire to just stick the sql in the code and move on. However, this is a bad practice for professional applications and it would lead to chaos on any development team that tried to follow it as a standard. I say leave that to the hobbyist.