column update function

[ben]

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_t wo, col_two)
, col_three = coalasce(@col_t hree, 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

[Erland Sommarskog]

ben (sa********@yah oo.com) writes:

update myTable col_two = coalasce(@col_t wo, col_two)
, col_three = coalasce(@col_t hree, 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.

T-SQL is not a language that lends itself to this sort of thing. There
is no way to tell whether a parameter was passed explicitly or not. You
can of course test for NULL, but it may have been an explicit NULL.

One alternative would be to have extra marker variables to tell whether
a parameter applies or not. It quickly gets bulky. It can be reduced to
a single parameter which is a bitmask, but that is cryptic and error-prone.

What we do in our update procedures is to pass all column values. But
then we typically have read all to the GUI and now we are sending them
back. If some operation updates only affects a few columns, that is
typically an individual UPDATE statement in a different procedure.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp