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.

I have a table in Microsoft SQL Server. Sometimes I need to update, and sometimes I need to insert. I could write 2 stored procedures:

InsertNewPerson
UpdatePertsonById

But I was thinking to write 1 stored procedure instead (SetPerson) which would do both (if there is an ID, it is an update operation , else insert).

Should I create one stored procedure (only one to maintain) or should I create two different stored procedures?

share|improve this question
    
Check this in between (posting and getting an answer, focused on 2008 version): Atomic UPSERT in SQL Server 2005 –  ypercube Dec 4 '12 at 8:11
    
@ypercube Thanks for helping :-) –  Royi Namir Dec 4 '12 at 8:11
    
The id is an IDENTITY column? If so I'd have two separate procedures. You could do a single procedure using MERGE but it would essentially have two distinct code paths and doesn't benefit you that much. I only use MERGE when I don't know in advance whether I need an UPDATE or an INSERT. –  Martin Smith Dec 4 '12 at 8:24
    
@MartinSmith it could be IDENTITY - but then , it will be used for : scope_ident ( for insert) , or by id (for update). –  Royi Namir Dec 4 '12 at 8:27
    
@RoyiNamir - But you know in advance whether you want an insert or update by virtue of whether you pass an id in? You never want to check if the id exists and if so update otherwise insert? –  Martin Smith Dec 4 '12 at 8:28
add comment

2 Answers

up vote 8 down vote accepted

As far as I understand it you are not actually talking about an UPSERT here just combining two different CRUD operations in one stored procedure.

CREATE PROC InsertOrUpdateYourTable @Id int = NULL OUTPUT,
                                    @Foo INT,
                                    @Bar VARCHAR(10)
AS
    IF @Id IS NULL
      BEGIN
          INSERT INTO YourTable
                      (Foo,
                       Bar)
          VALUES      (@Foo,
                       @Bar)

          SET @Id = SCOPE_IDENTITY()
      END
    ELSE
      BEGIN
          UPDATE YourTable
          SET    Foo = @Foo,
                 Bar = @Bar
          WHERE  Id = @Id
      END 

The benefit I see to this is that you don't have to maintain two separate parameter lists if the table structure changes. The disadvantage is that the single stored procedure now has two responsibilities and is somewhat less easy to understand.

I would generally opt for separating them into two stored procedures.

RE: "Can you elaborate how an upsert will look"

CREATE PROC UpsertYourTable
@Id int,
@Foo int,
@Bar varchar(10)
AS
MERGE YourTable WITH (HOLDLOCK)  AS T
        USING ( VALUES ( @Id, @Foo, @Bar ) ) 
              AS source ( Id, Foo, Bar)
        ON ( T.Id = source.Id )
        WHEN MATCHED 
            THEN 
        UPDATE SET
                Foo = source.Foo ,
                Bar = source.Bar
        WHEN NOT MATCHED 
            THEN    
        INSERT  (Id, Foo , Bar)
               VALUES 
               (@Id, @Foo , @Bar);

This assumes that Id is no longer an IDENTITY column. The reason for using HOLDLOCK is explained here.

share|improve this answer
    
Can you please elaborate how upsert will look like ? –  Royi Namir Dec 4 '12 at 11:49
    
Thank you very much Martin. –  Royi Namir Dec 4 '12 at 12:23
    
isnt the first query of mine is quicker ? –  Royi Namir Dec 4 '12 at 12:39
    
What first query? I don't see any queries in the question? –  Martin Smith Dec 4 '12 at 12:40
    
Sorry ! of yours ! (InsertOrUpdateYourTable ) –  Royi Namir Dec 4 '12 at 12:41
show 2 more comments

If you do want to use one procedure, you could use the MERGE statement.

Sample merge code:

create table testo(Id int identity(1,1) NOT NULL, somechar char(1), someint int, AddedTime datetime2(0), LastModifiedTime datetime2(0));
alter table testo add constraint [PK_testo] primary key(Id);    --Clustered index on target table.
--No index necessary on the source 'table' because we're creating it from parameters.

declare @Id int = 1;
declare @somechar char(1) = 'A';
declare @someint int = 42;

declare @results table (DMLAction sysname, Id int, somechar char(1), someint int );

MERGE dbo.testo AS Target
    USING
        (
            SELECT  
                @Id as Id,
                @somechar as somechar,
                @someint as someint
        ) AS SOURCE
    ON
        TARGET.Id = SOURCE.Id
    WHEN MATCHED
        THEN UPDATE SET 
            TARGET.somechar = SOURCE.somechar,
            TARGET.someint = SOURCE.someint,
            TARGET.LastModifiedTime = CURRENT_TIMESTAMP
    WHEN NOT MATCHED BY TARGET
        THEN INSERT
        (
            somechar,
            someint,
            AddedTime,
            LastModifiedTime
        )
        VALUES
        (
            SOURCE.somechar,
            SOURCE.someint,
            CURRENT_TIMESTAMP,
            CURRENT_TIMESTAMP
        )
    OUTPUT
        $action,
        inserted.Id,
        inserted.somechar,
        inserted.someint
    INTO
        @results;

select * from @results;

The biggest disadvantage is that performance will be worse (although you can mitigate performance problems through judicious indexing). Counteracting that, there are a few advantages.

The first advantage is that auditing is much more complete, as you get access to the SOURCE and TARGET record values if you need them. This is something you can't get from INSERT or UPDATE's OUTPUT clauses.

The second advantage to this approach is that you could create a table-valued parameter in your data layer instead of using individual parameters (like I did in this quick example) and use that as the SOURCE table. That means you could theoretically insert/update multiple records in the same call. Granted, you could do the same with separate INSERT and UPDATE statements in the same procedure or two separate stored procedures, but this lets you maintain one T-SQL statement instead of two.

Finally, if you wanted to put in the necessary effort, you could take the results of the Merge's OUTPUT clause (@results in the sample above), feed that back to .NET (or whatever your application layer is) and refresh your objects with the table results. That's not a trivial exercise, but it saves you another Get call. And if you insert multiple records simultaneously, you don't have to worry about trying to get the identity column results via SCOPE_IDENTITY(): the inserted pseudo-table already has the identity column's value for each record.

Again, most of the advantages are things you can replicate with separate insert and update statements, so this mostly boils down to the performance cost of MERGE versus the maintenance benefit from having to get it right in one location rather than two, and not needing separate insert/update logic in your business or data layers.

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.