Sql-server – How to handle update or insert situations

database-designinsertsql-server-2008t-sqlupdate

From time to time my stored procedures looks like 

create procedure handle_data 
    @fk int,
    @value varchar(10)
as
begin
    if exists (select * from my_table where id = @fk)
    begin
        update my_table
        set value = @value
        where id = @fk
    end 
    else 
    begin
        insert into my_table (fk, value)
            select @fk, @value
    end 
end

Probably there is fault in design of app which calls this stored procedure.
Should I avoid making apps which do same stored procedure and methods for inserting new data and also updating old ones?

Is there better way to achieve updating or inserting data in one approach?

I am using SQL Server 2005 / 2008.

Best Answer

A slightly more efficient way (which will do at worst one seek/scan instead of two against the existing data):

UPDATE dbo.whatever SET ... WHERE key = @key;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.whatever ...
END

MERGE may be tempting, however there are a few reasons I shy away from it.

  1. the syntax is daunting and hard to memorize
  2. you don't get any more concurrency than the above approach unless you intentionally add specific locking hints
  3. there are many unresolved bugs with MERGE and probably many more that have yet to be uncovered
Related Question