As a developer, the biggest benefit I get from using stored procedures is ease of development. By using stored procs exclusively, all applications have a well-defined interface to the database. Complex queries can be written by developers with more SQL experience and used by those with less experience. SPs also provide a degree of modularity and code reuse in applications. I've found that maintainance is decreased because its easier to do component-level testing and optimization for stored procs than it is for client-generated dynamic SQL. Any added performance is just an added bonus for me. Development time (up-front and down the road) is more costly than buying a beefier server.
It is true that sometimes dynamic SQL is necessary. When this is the case, I still try to encapsulate this within a stored procedure so that I get at least some of the benefits of testing and modularity.
Chris Cubley, MCSD, MCDBA