This is probably a common problem with a standard design pattern, but
I'm having trouble finding the solution.
I have a table with a lot of columns, for this example I'll just use
three but in reality its more like 20.
Create Table myTable (int col_one primary key, int col_two,
varchar(20) col_three) etc...
I want to write a sproc that allows updating of this column. Say I
have a sproc
create sproc myUpdate int @col_one, int @col_two, varchar(20)
col_three
as
update myTable col_two = @col_two, col_three = @col_three
where col_one = @col_one
then if I only want to update col_two I have to pass in the current
value of col_three so that it remains the same, which seems pretty
inefficient. so I could change it to:
as
update myTable col_two = coalasce(@col_two, col_two)
, col_three = coalasce(@col_three, col_three)
where col_one = @col_one
and then if I wanted to leave col_three the way it is then I could
just do
exec myUpdate 1, 2, NULL
the only problem here is that what if the value of col_three is
currently 3, and I want to set it to NULL? Under the current method,
setting someting to NULL is impossible
finally, I'd like to use parameter naming in my exec calls. that way
I can just say someting like
exec myUpdate 1, col_three=3
this would update col_three to 3 and leave the rest of the fields
untouched. you can see how handy this would be if you just want to
change a few of the fields in a table with a large number of columns.
I'm sure this has been done before, can somebody point me in the right
direction?
Thanks,
Ben