<< Use NHibernate (With Fluent NHibernate) To Test Your CRUD In One Line Of Code | Home | Resharper Tip: Navigating to a type >>
Conversation Starter: To Sproc or NOT to Sproc

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 07, 2009 7:44 PM | Filed Under [ Conversation Starters ]

Comments

Gravatar
# re: Conversation Starter: To Sproc or NOT to Sproc
Posted by Robz
on 5/7/2009 9:52 PM
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.
Gravatar
# re: Conversation Starter: To Sproc or NOT to Sproc
Posted by Troy Tuttle
on 5/7/2009 11:21 PM
Sprocs are generally more secure and have way better performance. How do I know? A guy told me this around 1998 or so.
Gravatar
# re: Conversation Starter: To Sproc or NOT to Sproc
Posted by Dan D.
on 5/8/2009 3:04 PM
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.
Gravatar
# re: Conversation Starter: To Sproc or NOT to Sproc
Posted by Robz
on 5/8/2009 5:24 PM
@Dan: I'm a fan of Mass Transit (a service bus) for applications to talk to each other...
Gravatar
# re: Conversation Starter: To Sproc or NOT to Sproc
Posted by Robz
on 5/8/2009 5:29 PM
@Troy: That makes me laugh. :D
Gravatar
# re: Conversation Starter: To Sproc or NOT to Sproc
Posted by Jim
on 5/18/2009 7:11 AM
I think I'd like to try a slew of SQL injection attacks against your apps. :o)
Gravatar
# re: Conversation Starter: To Sproc or NOT to Sproc
Posted by Lee Brandt
on 5/18/2009 7:17 AM
@Jim, you're welcome to try.:0) good data validation/cleansing goes a long way and since it has to be done anyway...
Gravatar
# re: Conversation Starter: To Sproc or NOT to Sproc
Posted by james peckham
on 6/20/2009 12:13 AM
@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! :)
Post Comment
Title *
Name *
Email
Url
Comment *