Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 sproc.
Should I avoid making apps which do same sproc 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 MSSQL2005/8.

share|improve this question
add comment

3 Answers

up vote 5 down vote accepted

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
share|improve this answer
    
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
add comment

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

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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