How to reuse selection result in SQL SERVER PROCEDURE

For example, code below, the arrow pointing two part, can the second one use the result from the first one, instead of querying the database again?

CREATE PROCEDURE GetInforEntries (@Count as int) AS
BEGIN   
    SELECT TOP (@Count) *
开发者_C百科      from Table_Entries 
     where Retrived IS NULL              <----                      

    IF @@ROWCOUNT > 0
    BEGIN

      UPDATE Table_Entries 
         SET Retrived = CURRENT_TIMESTAMP
       WHERE id IN (SELECT TOP (@Count) id <----
                      from Table_Entries 
                     where Retrived IS NULL)     

    END
END

---

Use a CTE in the UPDATE statement:

WITH sample AS (
  SELECT TOP(@count)
         te.*
    FROM TABLE_ENTRIES te
   WHERE te.retrieved IS NULL)
UPDATE sample
   SET retrieved = CURRENT_TIMESTAMP

There's no need to check @@ROWCOUNT/etc -- if there aren't any rows matching where TABLE_ENTRIES.retrieved is null, then the query has nothing to update.

Documentation

• UPDATE

---

You can declare a table variable or create a temp table to store the results of the first query.

DECLARE @MyTempTable (column-list)

INSERT INTO @MyTempTable (column-list...)
SELECT TOP(@Count) *
FROM Table_Entries
WHERE Retrieved IS NULL

-- display results of first query
SELECT *
FROM @MyTempTable

IF @@ROWCOUNT > 0
BEGIN

  UPDATE Table_Entries 
     SET Retrived = CURRENT_TIMESTAMP
   WHERE id IN (SELECT id <----
                  from @MyTempTable)     

This will only be beneficial if you want to display the results of the first query and if the number of rows returned in the first query improves performance in the update.