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
