I dont think that my position on this is based on being certified OR being book smart - I deal with this stuff every day and in fact I write a lot of code, enough to know what works and what hurts. True enough that you can mitigate the security threats to a large extent. Maybe your best developers do that. In practice few bother. Just as testing and code reviews are seen to 'take more time', security is rarely a primary consideration. If you owned an enterprise, which approach would make you sleep better at night, your DBA locking things down or relying on your developers to think security and get it right every time?
Changing tables doesn't always require changing procs, depends on why, right? If you need a value from the app, you'll usually have to change the calling code anyway.
At the end of the day you're writing almost the same amount of SQL, the question is where to store it. I've seen developers do hybrid approaches that stored all the sql in a table so they could change it without recompiling. Not my favorite for many reasons, but definitely configurable. So if you throw out security considerations, throw out performance (which varies depending on what you're doing), you're down to how long does it take you to maintain the code in a proc vs inline.
We can agree to disagree! To me, it just means I need to work harder at documenting why I think I'm right. In my view the entire industry spends too much time discussing it, we should come to a point where best practices are stated, the reasons supporting it are crystal clear, and we move on to bigger issues.
No, this is what I'm talking about:
"Changing tables doesn't always require changing procs, depends on why, right? If you need a value from the app, you'll usually have to change the calling code anyway."
How are you going to insert into a table or update it or select from it if you change the columns when you have hardcoded statements in stored procedures? Dynamic sql based on metadata requires no changes...NONE...suck in the new metadata and off you go.
Stored procedure based solutions are essentially hard coding. Nothing has ever been said good about that. Throwing all that code out in stored procedures is just job security and in the day when such practices will get your job outsourced to India faster than you can say pinkslip, data driven solutions are a much better approach.
Also:
"At the end of the day you're writing almost the same amount of SQL, the question is where to store it. I've seen developers do hybrid approaches that stored all the sql in a table so they could change it without recompiling. Not my favorite for many reasons, but definitely configurable. So if you throw out security considerations, throw out performance (which varies depending on what you're doing), you're down to how long does it take you to maintain the code in a proc vs inline."
NO WAY am I writing as much sql. My pages write the sql automatically. I never write the sql. The page builds the update statements, the select statements and the delete statements. My code figures out how to write the sql statement and writes it. That is the nature of dynamic sql. Why would I store off select statements and update statements? Think bigger...not just a single page for a single table, but a single page for ALL tables. Code that page and then see what I mean.
I'm not saying there aren't tradeoffs, but given the time saved, they are worth the tradeoff. Most noticeably, customizing a given data entry page is limited to defining different sections of the page and how many columns across the page is in each section.
Jim,
I'm just a cave man. Your system of displaying and updateing 150 tables from within two pages confuses and bewilders me.
It sounds like you are implementing some sort of table editor. In this very specific case you may be correct.
The systems that I write tend to be centered around specific actions that someone can take in a certain context. My systems tend to have
- an interface that helps them understand the action they are taking and how to take it
- business logic that checks the action that they take and processes it
- data access with definable units of database work to do
May I add this has been a guideline for quite some time now : 
The curse and blessings of dynamic SQL
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 31 through 45 (of 63 total)
You must be logged in to reply to this topic. Login to reply