sql - 选择然后更新这些记录，然后将它们返回到存储过程中

Question

我想要执行 SELECT 来获取 ID 列表，根据这些 ID 进行更新，然后返回这些记录。

我猜是这样的，我只是不知道语法:

  SELECT WebHookLogIDs = FROM WebHookLog 
                WHERE Processing=0
                    AND Processed=0
                        AND Paused=0
                            ORDER BY FailCount ASC, WebHookLogID DESC

    UPDATE WebHookLog
        SET Processing = 1
            WHERE WebHookLogID IN(WebHookLogIDs)

    SELECT * FROM WebHookLog 
            WHERE WebHookLogID IN(WebHookLogIDs)

Answer 1

我认为将数据放入临时表中比在其中插入数据更好

因为最后你想返回这些记录，所以你需要使用临时表

DECLARE @Table1 TABLE (WebHookLogIDs INT)

Insert into @Table1(WebHookLogIDs )
SELECT WebHookLogIDs  FROM WebHookLog 
                WHERE Processing=0
                    AND Processed=0
                        AND Paused=0
                            ORDER BY FailCount ASC, WebHookLogID DESC

    UPDATE WebHookLog
        SET Processing = 1
            WHERE WebHookLogID IN( select WebHookLogIDs from @Table1)

    SELECT * FROM WebHookLog 
            WHERE WebHookLogID IN(select WebHookLogIDs from @Table1)
    DROP TABLE @Table1