I'd like to add several clear cut benefits to using stored procs instead of dynamic SQL. I think my points have not been discussed much so far.
The problem of complexity has been solved many years ago. Put simply, Divide and conquer. By dividing any complex (coding) task, it becomes far easier to solve it.
This means that Stored Procs section off your application so that it is easier to test and easier to design. Stored procs provide small logic blocks that can be tested independently of compiled code. This separation of executable code from Database code makes debugging FAR faster and easier.
Just like graphical artists can improve a page because we now separate off the HTML from our actual code, Stored procs allow developers to work on the data side of their apps separately from their executable code. Mixing things, then, is in general, a limiting and bad practice.
If a program generates dynamic or concatenated SQL, you don't really know exactly what SQL is running unless you step through the exact code with the exact values. Sectioning off part of the work into a stored proc keeps things much simpler. It is possible to use the MS SQL profiler to debug dyncamic SQL but it requires DBA level permissions which are nearly always denied to developers.
Another benefit to Stored procs is that SQL is simply terribly messy to embed directly into code pages. At any of the large corporations I've worked at, a single table frequently had over 30 columns; some had 200 columns. Often, insert statements required values from other supporting tables so the SQL statements would be endlessly long. To embed that quantity of logic and SQL into a code page is a real mess.
And, update statements or select statements can take 4 or 5 times as many lines as insert statements.
It's far easier and more maintainable to view and work with SQL separately instead of having it mixed with code.
Also, using stored procs allows different applications and developers to use common database functions. SP's are a simple way to encapsulate common sections of business logic that are easily reusable despite language and platform differences.
I'm not at all convinced that stored procs are any faster for simple insert or select statements though. But in terms of keeping coding more organized, in terms of debugging, in terms of building larger more sophisticated applications, Stored procs are practically essential. If you can't agree with these points, ask most any DBA what he thinks. DBA's work daily with VERY large applications that use heavy stored procs.
As an experiment, lets consider the opposite view to see what value it might have. If we were to remove stored procs from the database entirely in any such large application, the code would become FAR more complex and far more difficult to modify. ANY database change would require searching thru large numbers of pages to make simple SQL statement changes. Only a developer would be able to make the changes and the code would need to be recompiled for every change. I've worked at companies where the lack of stored procs contributed to very messy and confusing code.
When executable code and database code are mixed together so tightly, it encourages a mixture of business logic and database logic.
From there it's only a small step to add in some GUI logic and we're right back in the days of spaghetti coding with a complete lack of separated layers.
A fundamental principle of coding is to use one or two word function names to execute more complex tasks. In the same way, stored procs are like functions that hide more complex tasks. Hiding complexity and dividing up complexity keeps coding simpler.
SQL is a separate language with it's own complexities that are best handled in it's own environment.
In summary, stored procs keep coding and debugging simpler by dividing the work up into layers.