Stored Procs vs. Dynamic SQL

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.

6 Comments

  • Of course this gets even more complicated when Yukon is released...then you can have CLR code in your DB...which makes life *interesting* :-)

  • middle ground ....

    I recently did an application where most of the core sql were in external files loaded with a caching mechanism ....

  • Most of us that debate the merits of "dynamic sql" instead of always using stored procs do NOT put sql in our code -- we use O/R mappers that generate the necessary parameterized sql at runtime. This results in far less code to write and maintain since there is now no sql anywhere -- not in the application or in the database -- writing and maintaining stored procs is still too much work. As for security, most apps I've seen do need quite a bit of application level security since you usually don't want to expose all screens and/or all buttons to all users -- and I can use roles for dynamic sql anyhow. Finally, if your DBA is there just to optimize your sql then you are seriously misusing that DBA -- a DBA is far more affective if he is an active participant in the database design. A properly designed database can then be optimized far easier -- index tuning, file groups, and other tricks of the trade -- far far more important than tuning simple stored procs that are a dime-a-dozen. So while I am thrilled you are getting hard-coded sql out of your apps, that hardly implies stored procs, and I seriously feel very sorry for your DBA that's left out of the process until its too late when he's given your stored proc to optimize after the fact. And please don't take this to mean that I believe stored procs are evil -- I have not said that at all -- I've simply said that you are making the wrong arguments, and that your DBA is being misused if you believe that is his role.

  • Since I guess Frans is following this thread - I have a question; how do you see O/R mappers changing for Yukon / ADO.NET 2.0?

  • "how do you see O/R mappers changing for Yukon / ADO.NET 2.0?"

    Not that much I think: the code will run inside Yukon instead of outside the db. The O/R mappers which use some code generation and db specific engines for SQL generation will have an advantage because they can write code especially for yukon (and for DB2 also, which also will have the CLR internally soon) so the code works with the ADO.NET 2.0 / yukon client directly.



    I haven't spend a lot of time yet on how an in-db dal should be accessed from a BL tier outside the db though, so there can be a problem in that area, not sure.

  • Thanks for the feedback guys. Thanks to Frans on the clarification of dynamic sql. The dynamic runtime type is exactly what I'm talking about and how it would probably be a maintainence headache to try to keep it in stored procs. Your sprocs would start looking pretty ugly.



    As for Yukon, I've been having discussions with people about it and how it makes it easier to put your BL inside the database now so some people might start doing that just because they can. I can imagine managed TSQL now becoming more "business like" when people find they don't have to keep passing information between layers. Of course the rest of us will probably try to leverage it the way it was meant to, but that has yet to be seen.

Comments have been disabled for this content.