SQL Server – Separate Stored Procedures for Inserts and Updates?

physical-designsql serversql-server-2008stored-proceduresupsert

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?

Best Answer

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.

Related Question