Rob Howard Started a War
On Stored Procedures v InLine SQL (heavy business logic in middle tier) in his blog on Monday..
I almost always use Stored Procedures. There is almost never a reason to use Dynamic SQL, but I am sure that there are times. My highlights:
- SPs are more secure. Most DBAs do not allow select permissions on any base tables-for obvious reasons. Using SPs gives you a nice abstraction layer. Don’t want someone accidentally deleting everything from a table, don’t create a SP to allow it.
- SPs can save your butt. What if a developer creates a dynamic SQL statement that looks ok but will not use an index, etc. And then in a few months your dynamic SQL is super slow since they are restricting on a billion row table without an index. Your procedure will never allow that if you say so.
- SPs are way easier to maintain in your code.
So what does Yukon with the ability to create SPs with C# or VB .NET do to all of this? Nothing, Microsoft still recomends TSQL for your data access/CRUD code.