Separate stored procedures for inserts and updates?

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.

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.