Update with Output Value

When running an UPDATE statement, you can not only update columns from the table, but you can also update variables. That gives you an interesting opportunity to update a single record, retrieving information about that record at the same time in a single query.

Here's a quick example where you could dequeue a record from a table, update its status, and return the ID of the record that was dequeued, without separate queries:

declare @ID int;

;with first_in_queue as
    select top 1 ID from dbo.Records
    where Status = 'NEW'
    order by CreateDate
update r
set r.Status = 'RUN', @ID = r.ID
from dbo.Records r
join first_in_queue on r.ID = first_in_queue.ID;

select @ID; -- The ID that was dequeued