How to debug merge in SQL Server?

Question

I am trying to learn how to use the MERGE statement. The following code compiles correctly:

ALTER PROCEDURE moto.procPM_UpdateLines @LineId As Int = null, @LineName As Varchar(100), @DeleteMe As Bit = 0 AS BEGIN MERGE moto.tblPMLine AS line USING (SELECT LineId, LineName FROM moto.tblPMLine) AS existsLine ON line.LineId = existsLine.LineId WHEN MATCHED AND @DeleteMe = 1 THEN DELETE WHEN MATCHED AND @DeleteMe = 0 THEN UPDATE SET line.LineName = @LineName WHEN NOT MATCHED THEN INSERT(LineName) VALUES(@LineName); END GO 

This is a very simple procedure, I know, but for some reason it does not generate any records when I use the following command.

  execute moto.procPM_UpdateLines null, 'First test', 0 

Is there any way to find out which of the options it follows, if at all?

---

The previous saved procedure has been fixed.

  MERGE INTO moto.tblPMLine AS T USING (SELECT @LineId as LineId, @LineName as LineName) AS S ON T.LineId = S.LineId WHEN MATCHED AND @DeleteMe = 0 THEN --UPDATE UPDATE SET LineName = @LineName WHEN MATCHED AND @DeleteMe = 1 THEN --DELETE DELETE WHEN NOT MATCHED THEN--INSERT INSERT (LineName) VALUES (@LineName) OUTPUT $action AS ChangesMade; 

Now it inserts, updates and deletes, and also returns the result.

Answer 1

You can combine MERGE and an OUTPUT clause to get some kind of “activity report” (or debug “print statements”) of what MERGE is doing - perhaps this will help you gain an understanding of what is going wrong.

See the Adam Machanic excellent blog post . OUTPUT or: how I learned to stop worrying and love MERGE , which shows what the method is and how to use it

It basically boils down to using the OUTPUT clause with the MERGE statement to get information about what is happening - something along the lines of:

 MERGE INTO ...... WHEN MATCHED THEN ....... WHEN NOT MATCHED THEN ...... WHEN NOT MATCHED BY SOURCE THEN ........ OUTPUT $action AS dml_action, inserted.x AS new_x, deleted.x AS old_x, inserted.y AS new_y, deleted.y AS old_y; 

Answer 2

 MERGE moto.tblPMLine AS line USING (SELECT LineId, LineName FROM moto.tblPMLine WHERE LineID = @LineID) AS existsLine 

Excuse me as I have not used MERGE . However, I do not see the code using the @LineID variable.