Late last year, Rob Howard and Frans Bouma got into a fairly heated debate about this. Frans' position was that stored procs were bad, Rob wanted everyone to use them. This resulted in a fierce blog by Frans, a rebuttal by Rob and hundreds of other bloggers taking either side.
Anyways, for me, it's back again. I'm rolling together several small applications and have come to a crossroads. Now understanding that I'm a working architect (i.e. a software architect that picks the hammer up once in awhile as opposed to the ones that just draw pretty pictures) so my TSQL is very limited and stored procs, well they're about as alien to me as anything. However the syntax isn't that odd (especially if you're writing simple sprocs) and there are too many advantages to pass it up.
Now there's always a lot of debate of TSQL vs sprocs and performance. Sure, complicated SQL can be a dog's breakfast when it comes to performance and you can just hand your stored proc over to your DBA and ask him to optimize it (what else is he/she there for anyways?). People will argue that simple statements like a SELECT blah FROM blah WHERE blah = @blah shouldn't be in the database. Why not? And who can tell you that the simple SELECT statement will never change. It only took me a few minutes to grasp the stored proc syntax so it's not all that bad.
As for things like SQL injection attacks or security, those things are always going to be an issue no matter where your code is. Just write good code and treat the database as a client rather than a consumer. There are some situations (like dynamic tables) where sprocs just fall down but I would take a closer look at your architecture. Do you really need dynamic tables or are you doing it to just cut down work imposed on you?
So basically, I'm looking to use sprocs even in small projects because:
- I don't want SQL Code in my application because I don't want an application to have to be recompiled when the database changes
- The application is there to view or be served up by the data, not the other way around so I don't want the application to have complicated security even in a situation where it's needed. It's much easier to apply role based security to the data than to worry about screens and buttons and domain objects that restrict access.
- I don't want to send a whack of unencrypted SQL across the network showing off my table names
- Reusability. Having stored procs available across my enterprise cuts down on the amount of code the developer needs to write.
- I can put together my sprocs and let my DBAs tune it independently of my application.
I'm finding a lot of advantages by getting my SQL out of my codebase and I believe the benefits outweigh the long term costs of maintenance. Everyone should come to his or her own conclusions on what is right for them. My preference is to use stored procs for any data manipulation. Even simple SELECT, INSERT, DELETE statements are targets as you never know what optimizations your DBA can make. Your mileage may vary.