Originally Posted By JosephCooney Here
I've read a couple of arguments for and against stored procedures vs. dynamic SQL.
http://weblogs.asp.net/rhoward/posts/38095.aspx
http://weblogs.asp.net/fbouma/posts/38178.aspx
http://weblogs.asp.net/RHoward/posts/38298.aspx
http://dotnetjunkies.com/WebLog/seichert/posts/3698.aspx
Here is my take.
Stored procedures are your methods against the "database" object. You write stored procedures for all the same reasons you add methods to objects (rather than manipulating the object internals themselves). If you want to change some aspect of the internals of the database, the only place you have to look to see what impact this will have is in your stored procedures. It gives you encapsulation and abstraction. Simple CReate-Update-Delete statements are generated for you. Wrapper classes for your stored procedures are generated. When you write a new procedure you re-generate your data tier. The only place where stored procedures fall down a little is writing performant ones that allow you to search tables on a large number of criteria, however there are code generation ways around this too. As I said in my comments on Rob Howard's orriginal post I love stored procedures and couldn't really imagine writing database driven apps without them, so I might be biased.