for performance reasons, we're considering changing our standard stored procedure for saving data (INSERT and UPDATE combined in a single stored proc):
ALTER PROCEDURE [dbo].[spCustomerSave]
(
@CustomerID int = null,
@CustomerName nvarchar(50),
@New_ID int output
)
AS
BEGIN
IF EXISTS(SELECT 1 FROM tblCustomer WHERE CustomerID = @CustomerID)
BEGIN
UPDATE tblCustomer
SET
CustomerName = @CustomerName
WHERE
CustomerID = @CustomerID;
SELECT @New_ID = @CustomerID;
END
ELSE
BEGIN
INSERT INTO tblCustomer(
Taalnaam)
VALUES(
@CustomerName)
SELECT @New_ID = scope_identity();
END
END
into a syntax using the MERGE statement:
ALTER PROCEDURE [dbo].[spCustomerSave]
(
@CustomerID int = null,
@CustomerName nvarchar(50),
@New_ID int output
)
AS
BEGIN
SELECT @New_ID = @idTaal;
MERGE dbo.tblCustomer as target
USING (SELECT @CustomerID, @CustomerName) as source (CustomerID, CustomerName)
ON (target.CustomerID = source.CustomerID)
WHEN MATCHED THEN
UPDATE SET
CustomerName = @CustomerName
WHEN NOT MATCHED THEN
INSERT (CustomerName)
VALUES(source.CustomerName);
SELECT @New_ID = scope_identity();
END
Question:
is this going to benefit us performance wise? (avoiding the initial SELECT on the primary key)
is this proper use of the MERGE statement? Most examples show MERGE statements in a scenario to execute multiple DML operations.