Procedimentos armazenados separados para inserções e atualizações?

Pelo que entendi, você não está realmente falando sobre um UPSERTaqui apenas combinando duas operações CRUD diferentes em um procedimento armazenado.

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 

O benefício que vejo nisso é que você não precisa manter duas listas de parâmetros separadas se a estrutura da tabela for alterada. A desvantagem é que o único procedimento armazenado agora tem duas responsabilidades e é um pouco menos fácil de entender.

Eu geralmente optaria por separá-los em dois procedimentos armazenados.

RE: "Você pode elaborar como um upsert ficará"

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);

Isso pressupõe que Idnão é mais uma IDENTITYcoluna. A razão para usar HOLDLOCK é explicada aqui .

Se você quiser usar um procedimento, pode usar a instrução MERGE .

Exemplo de código de mesclagem:

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;

A maior desvantagem é que o desempenho será pior (embora você possa atenuar os problemas de desempenho por meio de uma indexação criteriosa ). Contrariando isso, existem algumas vantagens.

A primeira vantagem é que a auditoria é muito mais completa , pois você tem acesso aos valores dos registros SOURCE e TARGET caso precise. Isso é algo que você não pode obter das cláusulas OUTPUT de INSERT ou UPDATE.

A segunda vantagem dessa abordagem é que você pode criar um parâmetro com valor de tabela em sua camada de dados em vez de usar parâmetros individuais (como fiz neste exemplo rápido) e usá-lo como a tabela SOURCE. Isso significa que você poderia teoricamente inserir/atualizar vários registros na mesma chamada. Concedido, você poderia fazer o mesmo com instruções INSERT e UPDATE separadas no mesmo procedimento ou dois procedimentos armazenados separados, mas isso permite manter uma instrução T-SQL em vez de duas.

Por fim, se você quiser fazer o esforço necessário, poderá pegar os resultados da cláusula OUTPUT do Merge (@results no exemplo acima), alimentá-los de volta para .NET (ou qualquer que seja sua camada de aplicativo) e atualizar seus objetos com os resultados da tabela. Não é um exercício trivial, mas economiza outra chamada Get. E se você inserir vários registros simultaneamente, não precisa se preocupar em tentar obter os resultados da coluna de identidade via SCOPE_IDENTITY(): a pseudotabela inserida já possui o valor da coluna de identidade para cada registro.

Novamente, a maioria das vantagens são coisas que você pode replicar com instruções de inserção e atualização separadas, portanto, isso se resume principalmente ao custo de desempenho do MERGE versus o benefício de manutenção de ter que acertar em um local em vez de dois, e não precisar separar insira/atualize a lógica em suas camadas de negócios ou dados.