Recently I have had to do a few variable updates to a table, and although I am aware of the MERGE statement (although need to catch up on all of that!), I also performed the following statement to optional update a table and wish to check if this is "a good idea" or has some hidden consequences that I not aware of.
So in my case, I pass a primary key to a table, however depending on if parameters passed are null or not, I update the column.. obviously if you had to ensure a forceful update (of a status etc.) then you would just update the column.. this is to save having multiple "IF / THEN" type structures..
create procedure sp_myprocedure
as
@id bigint,
@field1 int = null,
@field2 varchar(255) = null,
@field3 char(1) = null
begin
update my_table
set
field1 = case when @field1 is not null then @field1 else field1 end,
field2 = case when @field2 is not null then @field2 else field2 end,
field3 = case when @field3 is not null then @field3 else field3 end,
where
id = @id
end
Just after some thoughts of the above or is it best to pursue the MERGE statement for scenarios like the above?
Many thanks in advance,
This is fine although it can be written in a cleaner way.
update my_table
set
field1 = coalesce (@field1,field1)
,field2 = coalesce (@field2,field2)
,field3 = coalesce (@field3,field3)
where
id = @id and coalesce(@field1,@field2,@field3) is not null
You can also move the coalesce(@field1,@field2,@field3) is not null to a wrapper block
if coalesce(@field1,@field2,@field3) is not null
begin
update my_table
set
field1 = coalesce (@field1,field1)
,field2 = coalesce (@field2,field2)
,field3 = coalesce (@field3,field3)
where
id = @id
end
MERGE statement is not relevant here.
With MERGE the decision is if to INSERT, UPDATE or DELETE a record base on the non-existent/existent of a record with the same merge keys in the source/target table.
In your case it is always UPDATE.