can sql generate a good plan for this procedure?

Question

I have read that procs that do not always do the same thing per se, will not always have a good plan generated. That is (correct me if I'm wrong), if I have a proc that if the day is even it reads from table X otherwise it reads from table Y and the first time it executes is even, then the plan generated will be optimized for reading from table X and not Y, and sql will use that plan even when reading from Y, that imho is simple to understand and try to avoid, but what about procedures that act on the same table.

Like the sample below (obvioulsy a very simple example), if the first time it runs, and the item does exist, and subsequent runs generally do not (or vice-versa), would I end up with a "not so great" plan which would affect performance?

CREATE PROCEDURE dbo.MyProc
(
@data    VarChar(25),
@name    VarChar(25)
)

AS

    IF NOT EXISTS (SELECT * FROM dbo.MyTable WHERE myname = @name)
    BEGIN
        INSERT dbo.MyTable (myname, mydata)
        VALUES (@name, @data)
    END
    ELSE
    BEGIN
        UPDATE dbo.MyTabel
        SET mydata = @data
        WHERE myname = @name
    END

Answer 1

SQL Server 2005+ has statement level recompilation. Therefore, IIRC you'll get an optimal plan depending on branch.

However, your code isn't concurrency-safe. Two overlapping calls can get true for the NOT EXISTS: you get an error. This is why you use MERGE on SQL Server 2008.

Or assorted JDFI patterns for older versions. Also see Need Help Troubleshooting Sql Server 2005 Deadlock Scenario which has several options: it depends on your load too