1

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.

5

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
  • IF @@ROWCOUNT = 0 would probably be better for the insert, just in case you do something that requires two rows be inserted, a stored procedure that doesn't process the update based on the primary key for example. – mrdenny Apr 10 '13 at 22:52
  • @mrdenny I based that on where id= in the question - which certainly implies an identifier (or is very poorly named). – Aaron Bertrand Apr 10 '13 at 22:54
  • @AaronBertrand Then I should avoid adding SET NOCOUNT ON; inside my sprc ? – adopilot Apr 11 '13 at 7:44
  • In this case I agree either would work. However if this is used as a template for other procedures that may not get changed if needed. – mrdenny Apr 11 '13 at 7:57
  • @adopilot no, your stored procedure should still have SET NOCOUNT ON. This prevents chatty "n row(s) affected" messages from being sent to the client, but does not interfere with your ability to use @@ROWCOUNT. – Aaron Bertrand Apr 11 '13 at 15:13
4

MERGE statement will let you do it one command,

Create procedure handle_data 
  @fk int 
  , @value varchar(10)
AS
BEGIN

MERGE my_table AS target
USING (SELECT @fk, @value) AS source (fk, value)
ON (target.id = source.fk)
WHEN MATCHED THEN
    UPDATE SET value = source.Value
WHEN NOT MATCHED THEN
    INSERT (id, value)
    VALUES (source.fk, source.value);

END

More information about merge can be found at this article on MSDN

1

The MERGE statement was introduced in SQL Server 2008 to handle exactly this situation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.