Stored Procs vs Dirty SQL

Every once in a while I stumble across a blog that says stored procs are no longer the way to go in database development. I believe I see this for a few reasons

1) This view is often exposed by younger developers who, though extremely talented, do not have heavy enterprise data manipulation experience.

2) Advocates of this view typically are heavily into auto code generation tools, which don't play well with stored procs.

Here is an example of the type of scare crow testing I see (make a weak comparison, beat it up, then declare your opinion superior).

http://www.blackwasp.co.uk/SpeedTestSqlSproc.aspx

Wow, a select statement is almost as fast with dynamically generated SQL then with a stored proc, and even in some cases, a stored proc might be slower.

Well, if you work with data heavily on a regular basis, you wish you were writing select statements all day :) The case of comparison here is bogus. If select and basic CRUD on a single table is all you are using SQL for, you probably shouldn't talk about how your are an expert on the intricacies of SQL performance.

In my enterprise experience, it is not uncommon for a query will pull back data from five or six tables, which often times aren't tables at all but subqueries themselves. CRUD operations sometimes spam five or six tables. Putting all your code in a stored proc allows you to have a single place to change things, and often times updating the database in an enterprise environment is a lot easier then touching production code. I also don't believe that these types of queries are faster with dynamic sql. The dynamic SQL must be generated on a webserver or a client, so you take a hit there that isn't often factored into test data. Execution plans are generated at query runtime, while a stored proc has its execution plan cached. Big performance boost. Though, as pointed out, sometimes dynamically generated plans might run slightly faster. With complex SQL operations, I doubt that though. You also are slightly more open to SQL injection attacks by building your app using dynamic SQL, but if you know what you are doing you should be able to avoid vulnerabilities there. Stored procs also aren't invulnerable to attack either, they just tend to be more so. Also by using dynamically generated SQL you are loosing out on the CLR integration into SQL Server (if using MS tech), and depending upon what you are trying to accomplish using CLR stored procs might be a powerful, fast option that you are passing up.

So, if you are fortunate enough to be able to pull off simple queries for your application, and you have a robust app that is responsive and meets your client's needs, good for you. Keep at it. I'm not a huge fan of code generation tools, as it seems like when you go outside of the vanilla, you end up spending a lot of time and effort getting your non-vanilla task done. If you know the base tech really well, typically you already have a library of code that acts almost like a code generation tool. Plus there is the additional learning curve of a code generator, do I want to spend all the time learning something that's features might be included in future versions of visual studio? Or will my code gen tool be around five years from now? What if I want to pass my code of to a junior developer? Will I have to spend time teaching them, or allocating time for them to learn, a code gen tool that might be eclipsed by another tool that is more in vogue that wasn't around when my base code was written? But that is neither here nor there. Separate discussion.

I've written dynamic SQL apps before, and they have worked well. If your app works well with dynamic SQL and using dynamic SQL allows you to gain efficiencies by using your code gen tools, good for you. Keep at it. But save your lectures about stored procs being dead, because depending on your environment, they are far from dead. Often times stored procs are the only practical way to go.