In Response To Rob
Now I rarely bite... but Rob deserves a reply (and plus, until my 9pm meeting I have little else to do! :)
Rob said (in response to my comment on his blog):
@Casey: Come on homey - gimme a little something here. Your comment is just ridiculous - unit test - well I'll give ya that but strong typing - what does that mean when talking about SPs? I shoved the results into a collection - what more do you want?
Intellisense? Can I get you a beer with that? You're making us look like dopes Casey... there's a world out there without intellisense...
"Proper language constructs" - if you don't like SQL I can't help ya.
"... didn't often result in execsql..." so SPs jump out of your DB and rewrite themselves?
Normally I would let this comment go but I like you Casey - this comment has me baffled though. Perhaps you want to try again?
Well the things I brought up were :
Intellisense
I don't know if it makes *your* life easier, but is sure as hell saves me a lot of work, in fact SQLPrompt is a godsend of a tool, but more oddly, one of the things that is best about SubSonic (and is in the example videos/tutorials) as a strong selling point, is Intellisense. Can you exist without it? Sure, but it really sucks.
Funnily Rob, it would have been easier to catch me on 'well sure Intellisense is nice, but HQL doesn't have it' :)
Strong Typing
Again easier to get me on 'HQL doesn't have it' - but sure I like strong typing... I don't like having result sets inside a SP, that other parts of the SP may then manipulate, and the old 'execsql' get out that often appears to make it worse.
Having SPs return recordsets that I then have to wrap in a DTO, or inside (even worse) a DataSet, or worse still the dreaded Strongly Typed DataSet ... errrrghhhh!
Unit Testing
Well that point you accepted, but you still could have got me there by saying 'well I can wrap my SP inside a Unit Test too'. But when your unit tests start failing because your DBAs own the database, and they can't write unit tests ... who ya gonna call? Ghostbusters?
Proper Language Constructs
Nope, I don't like SQL much, it sucks. It is a black art. It should be left to DBAs or database developers who do nothing more than code SQL (I am a firm believer that people who code in Java, C#, C++ or any other similar language should be nowhere near SQL)
execsql
So ... I'm having a really hard time with my T-SQL ... the language constructs suck ... I want to make a Search procedure that takes 5 parameters (for example the fields on an employee record), and then produces a search for all employees that match those search parameters (what happens if the string is empty? are the fields to ANDed or ORed, etc) ... the T-SQL is becoming a monolithic nightmare ...
Solution? String concatenate within the SP (because your company policy is that all data access must be via SPs) ... create a SQL string that matches your queries, and use execsql.
Sounds ludicrous right? I must have worked at half a dozen places where this, and much worse, was done as a matter of routine.
And honestly - above all that - you have created another API ... and when an API becomes published (as opposed to public) - it essentially becomes immutable and fixed (COM has a legacy of IMyInterface2 for just this reason)
You create all your SPs - and you start to use them, and then when somebody wants to add a new field onto your employee class ... everything breaks. Sure it would do too (to a lesser degree) in an ORM, but at least it would be under your control.
I have been at multiple companies where the database has become the application (the Oracle world view, but on SQL Server), and the app servers and web servers have been relegated to UI and data transfer duty. That may not be so bad - except almost always the database has become the legacy application that you are struggling to maintain, with the assistance of less than helpful DBAs who are mostly interested in maintaining their database structure over your application needs.
There are *many* good arguments in favour of SPs, but maintainability shouldn't be in your list.