我正在尝试学习如何使用MERGE运算符.以下代码正确编译：
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
我知道这是一个非常简单的过程,但由于某种原因,当我使用以下命令时它似乎没有生成任何条目.
execute moto.procPM_UpdateLines null,'First test',0
有没有办法让我知道它遵循哪个选项,如果有的话？
以前的存储过程已修复
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;
解决方法
你可以结合MERGE和OUTPUT子句来获得MERGE正在做的某种“活动报告”(或调试“打印声明”) – 也许这将有助于你理解出了什么问题.
请参阅Adam Machanic的优秀博客文章Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE,其中显示了该技术以及如何使用它
基本上它归结为使用带有MERGE语句的OUTPUT子句来获取有关正在发生的事情的信息 – 大致类似于以下内容：
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;
