SELECT then UPDATE those records and then return them in STORED PROCEDURE

Question

I want to do a SELECT to get a list of ID's, do an update based on those ID's and then return those records.

Something like this I guess, I just do not know the syntax:

  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

I think its better to put data in Temp table, than insert data in it

because at the last you want to return those record back , so you need to use temp table

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

Answer 2

You can't do UPDATE with SELECT with the same SQL Statement. You can however, UPDATE with JOIN instead of selecting the ID's like this:

UPDATE w1
SET w1.Processing = 1
FROM WebHookLog w1
INNER JOIN WebHookLog w2  ON w1.WebHookLogID = w2.WebHookLogID 
                         AND w2.Processing    = 0
                         AND w2.Processed     = 0
                         AND w2.Paused        = 0;

Later, you can do another SELECT clause.