>
Or better yet, as I've said before, venture from the safe confines of the UT and post your views on a SQL Server forum.>
>Sheesh, I got all sorts of stick for daring to raise the issue of autospanning cursors right here, so he'd have to be a fool to go somewhere where you make it sound as if everybody knows they are right, apparently don't know WHY they are right, but certainly know how to bully and attack any heathen who dares to suggest their emperor has no clothes.
I have read your post and find most of it foolish in true usage.
SP or not SP it's going to generate a plan in SQL Server 2000 and newer. Sorry but that's just a fact.
Well crafted T-SQL is the difference between a poor query and a good one. By that I mean you know how to join tables and limit your result set because of knowledge of your indexes/constraints.
As a DBA I find that developers who have run wild with SPs forget that they wrote one and make another to do 90% of the same request. This turns into object bloat from a maintenance POV. My last employer had a 5+ year old system with over 3500 SPs.
You have to really determine if these SPs are enforcing your business rules, or are they doing task oriented work. Task Oriented to me = End of period processing, Commissions, batch oriented crap or the heavy lifting.
So if your enforcing business rules it belongs in a middle tier, right? If it's a data process -> flow, then it belongs in the data tier.
To have a class with gobs of code pieces that can get passed back individually for processing, you put yourself in an easier maintenance zone. Unless of course if your rules change like diapers on a baby then this is the worst design decision you could make. ;->
How lucky do you feel is the appropriate question you need to address for your SP usage and management. Do you see things changing? Then push that change to the back end in the beginning, and bring it to the middle tier when the OBTWs die down.
YMMV
__Stephen