Quite a thread. Let me throw some more gasoline on the fire here. First, on the whole Stored Procedure vs. Dynamic SQL issue, I am a big advocate of stored procedures. While many of my original reasons for this are not as valid today as they were a few years ago, one of the main ones still holds true-- maintenance. While you will certainly run into times where a client will switch platforms from Oracle to SQL Server, MySQL etc. or any combination thereof, the far more common event, in my experience, is that changes to the existing database are made.
In this scenario, stored procedures have a clear advantage. If the procedures are properly conceived along functional lines (i.e., this procedure gets the data to feed the daily output report), rather than simply dumping data from a given table, the procs can be changed to accommodate the new fields and structures with zero impact on the application code. Many companies, if not most, who are using my services, do not have a large in-house development capability. It is a much simpler and more cost effective process for a dba to make some changes in SQL than combing through thousands of lines of code to adjust the dynamic SQL and then recompile and redeploy the application. This whole discussion has gotten to micro focused, IMHO. We can argue for years about stored procs vs. dynamic SQL, where to put Business Logic, etc. Let's face it, the folks who post here are all pretty much on the ball in terms of knowledge, interest and dedication. We can differ on details, but we are all coming from a pretty solid knowledge base. There are legions of folks who don't share that dedication, interest or knowledge, as has been pointed out, and they can poison the water for the rest of us. What we have to realize is that the whole process requires an enormously different set of skills. On the database side, for instance, there is a big difference in skill between knowing how to tune a database, load balance between servers, etc., and being able to translate a specific problem domain into a sound data model. On the application side, deriving a sound application architecture involves a different mind set than translating the requirement into code, which both differ greatly from coming up with the optimal user interface. Properly documenting a project and managing the entire development process are also distinct skill sets. We tend to defend the role/skillset at which we excel, which is natural. The network guys (and gals) routinely blame the developers, who blame the dba's, who blame the business analysts, etc. Unfortunately, there appears to be a trend in business to demand all of these skill sets in one or two people. It is a rare project that I see where the client is willing to pay for a designated project manager. I am finishing an 11 month engagement on Monday for a governmental entity, where I was the business analyst, data architect, system architect, developer, UI designer, technical writer and project manager. Now, I know I am really pretty damn good at the analysis, database design and system architecture. I can write mean documentation. I'm pretty good with code, though not in the same league as some here, and am only fair as a UI designer, though I am getting better. What we do is really more art than science. While the academic discussions are great and beneficial, a lot of the theory goes out the window when you are confronted with a specific problem domain, budget and system limitations. Economics dictate a lot of what we do, and we have to wear too many hats far too often, IMHO. When I go to my family doctor, I don't expect him to be able to do a cardiac bypass or a knee replacement. Yet, in our world, those lines of demarcation between skillsets are fuzzy, at best. This is all a long winded way of saying that we need to be less dogmatic and evangelical when it comes to the micro issues. I tend to approach things from a business and data perspective first, because that is my background and my highest comfort zone. I tend to use web services and not remoting. Does that mean that remoting is bad? Of course not. As I read things here, I get different perspectives, and try to incorporate those into the approach I take on a given project. The reason I changed into this business 10 years ago <I am a recovering lawyer ;-)> was because of the blend of intellectual challenge and creative accomplishment that it offered. I learn something new every day, and that help keep me energized and feeling younger (at least mentally -- physically is another story entirely). Sure there are some absolutes, but fairly few. Adaptability is the key to survival, both in this business and in nature. The only way to adapt is to keep an open mind. OK, I'll stop being evangelical and dogmatic now . . . Jeff -----Original Message----- From: Unmoderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Frans Bouma Sent: Thursday, September 15, 2005 9:31 AM To: [email protected] Subject: Re: [ADVANCED-DOTNET] Business logic > Sure it does since the developers have to submit their stored > procs to me to be applied ;-) I have no control what they do > in their crappy code but I have almost total control over > what gets applied to the database. If I allowed dynamic SQL > in my stored procs (which requires permissions to the > underlying tables) then why would I care if they put it in > their business objects, front end or whatever. Where there > is no separation of duties and no control you are just about > guaranteed to have crap regardless of your beliefs on where > the business logic goes and the value of stored procedures. though what's your point then? Have only skilled people in your team who know what they're doing? Sure, but that's generic. :) What I find hard to understand is that you really don't give a **** about the application your procs are part of, as it seems. That's part of the problem: procs aren't a separate unit on a different planet controlled by another company, they're part of the application as they make up a (great) part of the functionality which is formed by the application. > Yes, while you are technically correct that it is > parameterization that prevents SQL injection I am pretty sure > you understand the point I was making. It is great that > there are many intelligent people on this list who can argue > so well, but I have found some of the hair splitting and nit > picking that goes on here detracts from the value of the discussion. yeah, all fine but this is of course BS. Mentioning 'Dyn. SQL is vulnerable to SQL injection attacks' and then later on saying that you meant a corner case (as in: bad practise goo generated only by vs.net or by an unskilled developer) why mentioning it at all? Of course concatenating values into query strings isn't good, but that doesn't mean dyn. sql is vulnerable to injection attacks. FB =================================== This list is hosted by DevelopMentor. http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
