Geeks With Blogs
Lee Brandt's Blog You're only as smart as your last line of code

bc_shout_thumb With the flat response from the series so far, I thought I’d stir the pot a little. Again, I am not trying to start a flame war, I just want to share my opinion and start a conversation about it.

I’ll start by saying that I am VERY comfortable using SQL. PL/SQL, T-SQL and even a bit of MySQL (ack). But after using NHibernate for the last year or so, I can honestly say that if I never write another sproc, I’ll be totally OK with that. I don’t think sprocs are evil, or ruin your application, for me it’s about testability. I like the fact that I can write a test that can check my query logic and never have to touch the database. I like the fact that I can test all four basic CRUD operations with one line of code! Most of all, I like doing it in C#.

There are times when there is no good substitute for a good sproc. But for most applications, I don’t see the point. SPECIALLY for CRUD operations.

What do YOU think?

Posted on Thursday, May 7, 2009 7:44 PM Conversation Starters | Back to top


Comments on this post: Conversation Starter: To Sproc or NOT to Sproc

# re: Conversation Starter: To Sproc or NOT to Sproc
Requesting Gravatar...
Sprocs are business knowledge in a database and much more painful to test than having that logic in code instead.

Sprocs are useful in very lmited cases, just never CRUD operations.
Left by Robz on May 07, 2009 9:52 PM

# re: Conversation Starter: To Sproc or NOT to Sproc
Requesting Gravatar...
Sprocs are generally more secure and have way better performance. How do I know? A guy told me this around 1998 or so.
Left by Troy Tuttle on May 07, 2009 11:21 PM

# re: Conversation Starter: To Sproc or NOT to Sproc
Requesting Gravatar...
If you can contain all database access to one application, I think it's easier to deal with everything all in one place. If you have tools that manage the sprocs integrated with your other code-management tools, it might not be much of a pain. You just have to make sure that the sprocs in development match those in production, staging, QA, ...

If multiple applications are touching the same database directly (bad idea). They need a common sproc interface, or any database design errors or changes are multiplied in complexity. I'm a fan of using RPC for cross-application integration instead of letting them talk to the same database.
Left by Dan D. on May 08, 2009 3:04 PM

# re: Conversation Starter: To Sproc or NOT to Sproc
Requesting Gravatar...
@Dan: I'm a fan of Mass Transit (a service bus) for applications to talk to each other...
Left by Robz on May 08, 2009 5:24 PM

# re: Conversation Starter: To Sproc or NOT to Sproc
Requesting Gravatar...
@Troy: That makes me laugh. :D
Left by Robz on May 08, 2009 5:29 PM

# re: Conversation Starter: To Sproc or NOT to Sproc
Requesting Gravatar...
I think I'd like to try a slew of SQL injection attacks against your apps. :o)
Left by Jim on May 18, 2009 7:11 AM

# re: Conversation Starter: To Sproc or NOT to Sproc
Requesting Gravatar...
@Jim, you're welcome to try.:0) good data validation/cleansing goes a long way and since it has to be done anyway...
Left by Lee Brandt on May 18, 2009 7:17 AM

# re: Conversation Starter: To Sproc or NOT to Sproc
Requesting Gravatar...
@Jim, you can still use parameterized queries without sprocs. LOL @Troy... i just had this argument with a co worker and said "Oh they're faster huh? so you've actually looked at the execution plan and it's different?" he said "No" and when i said 'let's do it then" he declined. But they're more secure right? Only because most SQL DBAs are just as lazy as us programmers and won't make granular roles for apps and so just adding "Execute" for little one off logins is easier! SO FUNNY! sprocs are so 1998... good one troy! :)
Left by james peckham on Jun 20, 2009 12:13 AM

Your comment:
 (will show your gravatar)


Copyright © Lee Brandt | Powered by: GeeksWithBlogs.net