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.
Best Answer
This is something you can easily test for yourself, but in my testing, no, there is no significant overhead in calling a stored procedure across a database boundary (I am sure you could make something noticeable though if you tried hard enough).
However, I would say that a stored procedure should live closer to the data that it manipulates than the object(s) that call it. This way, if you move the database, all that breaks is the remote calls to that procedure, rather than all of them.