September 15, 2008 at 3:08 am
I'm thinking about a SP to update a table. I'm thinking about how to go about it best and would appreciate some hints.
Not all of the attributes will be updated in the same UPDATE statement and how to distinguish between attributes which are updated and those that are not. My first thought is to use a CASE for each attribute to decide it needs to be updated. Those with NULL don't need to be updated, others do. But how to use this in an UPDATE statement?
In a trigger it would be easy to use INSERTED and DELETED, not so in a SP. I could possibly just call an UPDATE to the table and then use a trigger with supplied values to decide which attributes should be updated with INSERTED and DELETED.
Is this a good approach? I can't help thinking it must be easier to do this. I can't find the right worths to get a meaningful hit with Google. Any help apreciated.
Greetz,
Hans Brouwer
September 15, 2008 at 3:22 am
Hi,
couldnt you use the structure :
UPDATE TABLE1
SET VAL1 = ISNULL(@PASSEDINVAL1,VAL1),
VAL2 = ISNULL(@PASSEDINVAL2,VAL2)
WHERE PKEY = @PKEY
I suppose this still leaves the issue of actually wanting to NULL out a value in a row - would that be a problem ?
Is this the kind of thing you meant ?
Thanks,
Andy
September 15, 2008 at 9:52 am
This might actually be the solution. Tnx a lot.
Nullability is not a problem here.
Greetz,
Hans Brouwer
September 15, 2008 at 2:51 pm
Normalizing of your attribute storage would be much more effective and much more simple in terms of coding.
Yes, it would be not so comfortable for editing from EM.
But you data to be managed by users of your application, not by you on the background. Right?
_____________
Code for TallyGenerator
September 15, 2008 at 8:56 pm
Sergiy (9/15/2008)
Normalizing of your attribute storage would be much more effective and much more simple in terms of coding.Yes, it would be not so comfortable for editing from EM.
But you data to be managed by users of your application, not by you on the background. Right?
I'm not sure I'm following you here Sergiy, can you expand on your thoughts on this matter?
I'd also like to know how you preffer to code your own update statements. I too have some tables with loads of nullable data (even in normalized form). I'd love to save the network the trouble of sending 50 parameters on each SP call. But I don't know the impact of using that isnull command or anything similar.
Thank you for your thoughts on this.
September 15, 2008 at 9:34 pm
Ninja's_RGR'us (9/15/2008)
I'm not sure I'm following you here Sergiy, can you expand on your thoughts on this matter?
UPDATE AttributeTABLE
SET VAL = @PASSEDINVAL
WHERE PKEY = @PKEY
AND AttribID = @AttribID
BTW, it lets you assign NULL or delete specified record when attribute needs to be dropped.
_____________
Code for TallyGenerator
September 16, 2008 at 5:45 am
Thanks Sergiy, that answers part one of the question...
How do you deal with update SPs on table with a ton of column?
Do you preffer to have the dev only pass the columns that were changed to the SP, or do you have them send all the parameters for all the columns and deal with it that way?
September 16, 2008 at 6:12 am
I don't deal with updates on tables with a ton of column.
It would compromise my professional reputation.
I don't do crap.
_____________
Code for TallyGenerator
September 16, 2008 at 6:50 am
Thanks for all the usefull info man.
September 16, 2008 at 2:04 pm
I believe consistent deadlock statistics will add to my info something even more useful as soon as you've got 2 or more users start to do updates on the "table with tons of columns".
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply