sql - MSSQL 更新 : output value before update

Question

有一个包含 IDU (PK) 和 stat 列的表。如果 stat 的第一位是 1，我需要将它设置为 0 并仅在这种情况下运行一些存储过程，否则我什么都不做。

这是对此的简单查询

DECLARE @s INT
-- get the current value of status before update
SET @s = (SELECT stat FROM  myTable
          WHERE IDU = 999999999)
-- check it first bit is 1
IF (@s & 1) = 1  
BEGIN
    -- first bit is 1, set it to 0
    UPDATE  myTable
    SET status = Stat & ~1
    WHERE IDU = 999999999
    -- first bit is one, in this case we run our SP
    EXEC SOME_STORED_PROCEDURE
END 

但我不确定这个查询是否是最优的。我听说过 UPDATE 查询的 OUTPUT 参数，但我发现了如何获取插入值。有没有办法获取插入前的值？

Answer 1

是的，OUTPUT子句允许您在更新之前获取以前的值。您需要查看 deleted 和 inserted 表。

DELETED

Is a column prefix that specifies the value deleted by the update or delete operation. Columns prefixed with DELETED reflect the value before the UPDATE, DELETE, or MERGE statement is completed.

INSERTED

Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.

-- Clear the first bit without checking what it was

DECLARE @Results TABLE (OldStat int, NewStat int);

UPDATE myTable
SET Stat = Stat & ~1
WHERE IDU = 999999999
OUTPUT
    deleted.Stat AS OldStat
    ,inserted.Stat AS NewStat
INTO @Results
;

-- Copy data from @Results table into variables for comparison
-- Assumes that IDU is a primary key and @Results can have only one row

DECLARE @OldStat int;
DECLARE @NewStat int;

SELECT @OldStat = OldStat, @NewStat = NewStat
FROM @Results;

IF @OldStat <> @NewStat 
BEGIN
    EXEC SOME_STORED_PROCEDURE;
END;