SQL Server Stored Procedure to Select/Update/Return

I am trying to write a SQL Server stored procedure that selects the top row from a table, updates a column in that row, an开发者_如何学Cd then returns this row with the changes.

For example:

1. row = SELECT TOP 1 FROM Requests
2. UPDATE Requests SET row.Locked = True
3. RETURN row

I can't seem to get this right..can someone point me in the right direction?

update top(1) Request
set Locked = 1
output inserted.*
where Locked = 0

A version when you need order by to get the row you want to modify. Here you will set Locked=1 for the highest DateField value where Locked = 0

Test data

create table Request(ID int, Locked bit, DateField datetime)
insert into Request values (1, 0, '20010101')
insert into Request values (2, 0, '20010102')
insert into Request values (3, 0, '20010103')

Update statement with cte

;with cte as 
(
  select *,
    row_number() over(order by DateField desc) as rn
  from Request
  where Locked = 0
)
update cte
set Locked = 1
output inserted.ID, inserted.Locked, inserted.DateField
where rn = 1

Result

ID          Locked DateField
----------- ------ -----------------------
3           1      2001-01-03 00:00:00.000

Or you could do this if you want to use inserted.* instead of specifying the fields in output clause.

update Request
set Locked = 1
output inserted.*
from Request as r
  inner join
    (select ID, row_number() over(order by DateField desc) as rn
     from Request
     where Locked = 0) as rs
    on r.ID = rs.ID and
       rs.rn = 1

You can use an OUTPUT clause:

 DECLARE @Results TABLE (SomeField INT)

 UPDATE TOP (1) Requests
 SET Locked = 1
 OUTPUT inserted.SomeField INTO @Results (SomeField)

 SELECT * FROM @Results