Update only what passed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tshad - 02 Jul 2009 22:37 GMT

In 2008, is there a way to update only what was passed.

Something like

CREATE PROCEDURE UpdateTable

@Field1 int = null,
@Field2 string = null,
@Field3 int = null

Update table
SET
Field1 = @Field1,
Field2 = @Field2,
Field3 = @Field3
...
Where...

where only the fields I pass gets updated?

If I pass @Field2 only, only that variable gets updated.

I have some tables that are 200-300 variables and I may only update 3
fields.

Thanks,

Tom

Reply to this Message

Plamen Ratchev - 02 Jul 2009 23:27 GMT

You can do:

UPDATE Table
SET field1 = COALESCE(@field1, field1),
field2 = COALESCE(@field2, field2),
...

Signature

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

tshad - 27 Jul 2009 23:16 GMT

> You can do:
>
> UPDATE Table
> SET field1 = COALESCE(@field1, field1),
> field2 = COALESCE(@field2, field2),

That would work.

Of course, if you wanted to send a null, it wouldn't. But I don't see how
you would get around that.

Thanks,

Tom

> ...

Reply to this Message

Bob Simms - 02 Jul 2009 23:54 GMT

> In 2008, is there a way to update only what was passed.
>
[quoted text clipped - 24 lines]
>
> Tom

Ugly bit of coding, but I think the below might work. it's untested. make
sure at the beginning of the proc you thoroughly test the values, though, as
using EXECUTE can make you vulnerable to injection attacks.

declare @fields varchar(1000) = 'update table ('
declare @values varchar(1000) = ' values('

if @field1 is not null
begin
SET @fields += 'field1,'
if not isnumeric(@field1)
set @values += '''' --start the value with a quote if not numeric
set @values += @field1
if not isnumeric(@field1)
set @values += '''' --end the value with a quote if not numeric
set @values += ','
end
if @field2 is not null
begin
SET @fields += 'field2,'
if not isnumeric(@field2)
set @values += '''' --start the value with a quote if not numeric
set @values += @field2
if not isnumeric(@field2)
set @values += '''' --end the value with a quote if not numeric
set @values += ','
end
....
....
SET @fields = LEFT(@fields, LEN(@fields -1) -- get rid of last comma
SET @fields = LEFT(@fields, LEN(@fields -1)
SET @fields += ') '
SET @values += ')'

EXECUTE (@fields + @values)

Signature

Bob Simms
Senior Learning Consultant
QA - transforming performance through learning
www.qa.com

Reply to this Message

Uri Dimant - 03 Jul 2009 07:58 GMT

Take a look at ISNULL or COALESCE system functions

> In 2008, is there a way to update only what was passed.
>
[quoted text clipped - 24 lines]
>
> Tom

Reply to this Message