sql-server – 如何在SQL Server中调试合并？

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;

现在它插入,更新和删除,以及返回输出.

解决方法

请参阅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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!