There are many great topics of debate in software development. DataSets Vs. Objects, Smart Clients Vs. Web Apps, and Stored Procs Vs. Dynamic SQL. Many have argued that writing stored procedures is rigid and doesn't allow for an easy way to build detailed where clauses. Furthermore, SQL Server 2000 caches execution plans so in theory SQL Statements should be optimized if used often. Finally, .NET allows for parameterized queries helping to protect dynamic queries from SQL Interjection.
Even with these arguements against stored procedures the arguements for them are greater. First, Stored Procs use less bandwidth to do the same amount of work as a dynamic query. For example, if my client application is using dynamic SQL then it has to pass the entire SQL statement to the SQL Server for processing. In contrast, if the client application is using stored procedures all it has to do is call the stored procedure by name and pass to that procedure any required parameters. By using stored procedures less information is sent from the client to the server to do the same amount of work. Note: This can also be viewed as a security benefit because less information is being transmitted accross the wire that describes the exact design of your database. Next, Stored Procedures can be controlled by database security. That is a user be denied access to the tables within a database but be granted access through stored procedures. The stored procedures become the only way that user can read, add, update, or delete data from the database. Using stored procedures provides a useful layer of abstraction and a cleaner application design It is possible for a change to be made within a store procedure that will not require modifications to the application. If the client application were using dynamic sql such changes could only be made through a code change and then a redployment. Store Procedures can implement batch processing easier and more effectively than dynamic SQL.