One of the recurring discussions among developers of data-driven applications is the choice between stored procedures and dynamic SQL.
Last time I stumbled upon this subject was when I started reading this post about NHibernate at Karl Seguin’s blog. Years ago one of the longest discussion was caused by this post from Frans Bouma. This is an another typical post about the subject I saw today.
I’m not going to start another rant about which is the right choice for everything, as there simply isn’t any ultimate answer. Deep Thought could argue with this, but the answer would probably take millions of years, so who cares. 
Use object-relational mapping for the generic stuff
My recommendation is to use dynamic SQL for the most database access methods (say, 80-90%), when coupled with a solid object-relational mapping tool or some code generation tool.
Especially with large databases that have many tables it wouldn’t be wise to code all the SQL by hand (sprocs or inline). Add tight schedules and changing requirements/database schemas and long days are guaranteed.
Usually majority of the data-access methods for normal, transactional databases are basic CRUD-operations, so why bother coding sprocs when they don’t bring any advantages?
Better choice is to use LINQ, Entity Framework or a good third-party object-relational mapper to reduce routine coding and to get lazy-loading etc.
Use stored procedures for the heavy stuff
No matter how useful OR-mappers can be, I still think that for the rest of the data access (the remaining 10-20%) stored procedures are a better option.
For complicated queries like searches and reports you often need more sophisticated SQL than what the generic tools can generate. I’m talking about queries or updates that span many tables, aggregate data or those that would require lots of round-tripping when using dynamic SQL and other client-side alternatives.
Of course you can combine all the data from automatically generated collections, but it would mean very chatty way to collect the data. With sprocs you can often get all the required information in one access to the database.
In cases like these it is better to fire up the good old Management Studio (or Query Analyzer) and optimize the queries for better results. While tuning, you can easily check the Execution Plans, index usage and logical reads (with STATISTICS IO option on).
I’m not saying that stored procedures are always automatically faster than similar dynamic SQL-clauses (they are not). My point is that manual tuning SQL by hand brings more performance as long as you know what you are doing. Crappy SQL in stored procedures (cursors, temp tables etc) won’t bring any benefits. Also, business logic belongs to the business logic layer.
When you notice that some part of the application is performing slowly, moving and optimizing that part of the code as a stored procedure could help. SQL Server Profiler is a good tool for finding slow-performing queries.
Conclusion
Like with almost everything, choosing the right tool for the right job applies here as well. Better get the best of both worlds than choosing blindly one over other.
For normal (CRUD) database operations, use object-relational mapping. However, make sure that the tool can use sprocs when needed or write helper classes for sproc-access manually as sometimes it is better to optimize the queries manually.
)
Posted by jemm 
