The worm turns: Embedded SQL vs Stored Procs

Written by James Kovacs about 5 years ago.

Historically Microsoft recommended stored procs because they gave much better performance. Query optimizers in SQL Server 2000 and 2005 have improved dramatically and this is no longer much of an issue. The biggest hassle with stored procs is you have two places to maintain and version code — namely your application and the database. Versioning is much more painful because even without database schema changes, you need the correct set of stored procs for the code base that is running against them. When you’re pulling apart the result set, you need to compare the columns in the stored proc select statements against the columns that your DataReaders are expecting. A trivial task, but mind-numbingly boring. It’s easier if the select is in close proximity to the code that parses it. Personally I’m a big fan of having an ORM tool such as NHibernate or LINQ generate the dynamic queries for me and perform the parsing. I honestly think that’s the way of the future and hand-optimized stored procs will be used for perf critical operations when needed.

And Microsoft doesn't provide a good way of organizing and managing Stored Procedures into folders.