SQL MERGE with variables

Question

How can I use the merge function with @variables in Microsoft SQL Server Management Studio 2008 r2?

All of the examples that I have searched for online use tables to merge into tables. Is this possible with variables?

For Example:

CREATE PROCEDURE UpdateOrder
            @id int,
            @payment_date smalldatetime,
            @amount numeric(10,2)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;



    MERGE order AS o
    USING @id, @payment_date, @amount
    ON @id = o.id
    WHEN MATCHED THEN 
                    UPDATE SET 
                               o.payment_date = @payment_date,
                               o.amount = @amount
                               o.last_updated_on = GETDATE()
    WHEN NOT MATCHED THEN
        INSERT(o.id,o.payment_id,o.amount)
        VALUES(@id,@payment_id,@amount);

This does not work. Thanks!

Answer 1

You can use the VALUES clause to make a single row derived table then the rest is as usual.

MERGE order AS o
USING (VALUES (@id,
               @payment_date,
               @amount)) AS s(id, payment_date, amount)
ON s.id = o.id
WHEN MATCHED THEN
  UPDATE SET o.payment_date = s.payment_date,
             o.amount = s.amount,
             o.last_updated_on = GETDATE()
WHEN NOT MATCHED THEN
  INSERT(o.id,
         o.payment_id,
         o.amount)
  VALUES(s.id,
         s.payment_id,
         s.amount); 

You might want to read Use Caution with SQL Server's MERGE Statement as well though.

Answer 2

Or you can simply check for existence of record and then do the update/insert

IF EXISTS(SELECT * FROM [order] WHERE ID = @id)
 BEGIN
     UPDATE [order]
      SET payment_date = @payment_date,
          amount       = @amount,
          last_updated_on = GETDATE()
     WHERE ID = @id
 END

ELSE 

 BEGIN
   INSERT INTO [order](id,payment_id,amount)
   VALUES(@id,@payment_id,@amount)
 END