October 13, 2009 at 10:15 am
So I have to work on some stored procs for a development project. Basic CRUD stuff.
I'm running into a brick wall with a developer who demands that when he calls the INSERT, or UPDATE procedure that he should have a parameter for every field in the table.
For INSERTs, I more or less agree, though I don't think I need parameters for such things as the record creation date (since I can get that via getdate() )
But for updates there are several fields that cannot be changed (Creator of the record being one, date of creation being another, etc).
Yet I'm being told by the developer to expose these as parameters and just not use them.
My inner DBA sense screams not do to this... Should I listen to it, or disregard it?
October 13, 2009 at 10:25 am
Ask the developer to explain exactly why that isn't a stupid idea.
October 13, 2009 at 10:37 am
Michael Valentine Jones (10/13/2009)
Ask the developer to explain exactly why that isn't a stupid idea.
The answer to this one is that he loops through an object and passes all the object values as parameters to the stored proc (in the case of INSERT or UPDATE) ) and expects all the possible fields that are in his object to be exposed even if not used by the sproc at all.
October 13, 2009 at 12:26 pm
Essentially he is saying: "I want you to do more work, so I don't have to do as much." You COULD provide him with a list of the untouchable columns and he could exclude those in his loop. How charitable are you feeling?
Alternatively, have him pass the data as XML? What happens when the target table changes? You have to add more parameters but his code doesn't have to change? This could be an opportunity for you to make your code dynamic just like his.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 13, 2009 at 12:45 pm
I'd say go ahead and include them. It means a little extra RAM being allocated, but it doesn't hurt anything beyond that.
It allows the dev to build a more generic data access layer, since it can be built to dynamically operate off of metadata, instead of being hard-coded.
And, if you select from sys.columns, and do a tiny bit of string manipulation, you can generate a list of the parameters and their data types pretty efficiently. That means it's not actually much more work for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2009 at 12:59 pm
GSquared (10/13/2009)
I'd say go ahead and include them. It means a little extra RAM being allocated, but it doesn't hurt anything beyond that.
Okies... it wasn't about work for me anyways... Something about exposing parameters that will never be used for anything just seemed wrong to me. I don't know why but it just seemed wrong.
October 13, 2009 at 1:12 pm
Think about this... the unused parameters do have a purpose. They are being used to save coding effort in the calling application.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 13, 2009 at 1:17 pm
Bob Hovious 24601 (10/13/2009)
Think about this... the unused parameters do have a purpose. They are being used to save coding effort in the calling application.
Ok since you put it that way... Again I was more concerned about wasting processor cycles (with decoding parameters that aren't used) and memory (though I guess arguing over 154 bytes per SP call is probably pointless in this day and age) and bandwidth (gotta send those 154 bytes each time you call it).
And my wrongness alert went up. Something about the above seemed to me to be a potential issue for something... but I guess the wrongness alert was just tired this time 🙂
October 13, 2009 at 1:20 pm
You might be right, but I think it's worth the risk. If you find it really does cause problems, then tell your developer that he has to do it the hard way.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 13, 2009 at 2:27 pm
You have valid points. It does cost some bandwidth, and resources. RAM does have to be allocated to hold null values that will never be populated. I haven't worked on systems where that would matter, but that doesn't mean they never will.
Talk over that point with the dev, see if he goes, "oh, wait, you're right, never mind". Could happen. If not, then go ahead with them on your test system, do some load testing, and if it hurts, handle appropriately, and if it doesn't, go ahead with it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2009 at 3:14 pm
GSquared (10/13/2009)
I'd say go ahead and include them. It means a little extra RAM being allocated, but it doesn't hurt anything beyond that.It allows the dev to build a more generic data access layer, since it can be built to dynamically operate off of metadata, instead of being hard-coded.
And, if you select from sys.columns, and do a tiny bit of string manipulation, you can generate a list of the parameters and their data types pretty efficiently. That means it's not actually much more work for you.
If they are going to build the parameters dynamically from metadata, they could generate them from the stored procedure parameter list, instead of the table column list. That would actually make sense, because it would match the stored procedure.
It would also have the benefit of not being stupid.
Does the developer also include all the table columns on their input form so that they can have a generic presentation layer?
October 13, 2009 at 4:03 pm
Ahhhhh.... you beat me to it Michael! I just remembered doing a function for one of our developers to list the parms in a stored proc.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 14, 2009 at 6:40 am
I sure as hell hope not.
This particular developer is a bit difficult to work with, but has the advantage of politics.
I think it's silly to expose these parameters but if it makes him happy I'm almost willing to do it to keep him out of my hair.
October 14, 2009 at 8:00 am
Michael Valentine Jones (10/13/2009)
GSquared (10/13/2009)
I'd say go ahead and include them. It means a little extra RAM being allocated, but it doesn't hurt anything beyond that.It allows the dev to build a more generic data access layer, since it can be built to dynamically operate off of metadata, instead of being hard-coded.
And, if you select from sys.columns, and do a tiny bit of string manipulation, you can generate a list of the parameters and their data types pretty efficiently. That means it's not actually much more work for you.
If they are going to build the parameters dynamically from metadata, they could generate them from the stored procedure parameter list, instead of the table column list. That would actually make sense, because it would match the stored procedure.
It would also have the benefit of not being stupid.
Does the developer also include all the table columns on their input form so that they can have a generic presentation layer?
Michael's version also has the advantage of not instantly breaking your site / app any time you modify the base table. (Assuming the dev was pulling up a dynamic list of columns) If he's not pulling up a dynamic list of columns, then this is pointless overhead and he needs to quit being lazy. This seems like one of those things that when you do it to save a headache now, you get your head slammed into a brick wall later as a reminder as to why you shouldn't have.
I'm not very familiar with XML, but that idea seems like it could be the winner here. I'm thinking you could have each insert/update SP take in a single XML parameter, then have a standard parsing function that spits out a TVF from that XML. Unfortunately, dynamic SQL is the only way I can think of to keep this truly up to date with added fields, so injection may be a concern.
If you didn't want to make it dynamic in this nature, the SP would still function, it just wouldn't update the additional fields.
October 14, 2009 at 8:09 am
Iterating through the parameters would definitely be better. Heck, even I managed to do that in an Access app I used to operate, and I'm far from a skilled dev (outside of T-SQL), so it can't even be all that difficult to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply