title: SQL Server stored procedure select top 1 and update in one statement?
body: <p>I'm trying to select the top 1 record with <code>recordStatus=0</code> and at the same time update the recordStatus column to 1 in a stored procedure, My question is Can it be done in one select statement or do I have to use 3 statements?, here's what I got:</p>
  
  <pre><code>PROCEDURE sp_getRecord
     @recordID varchar(30) = NULL
  AS
  BEGIN
     SELECT TOP (1) @recordID = recordID
     FROM TABLEA
     WHERE recordStatus = 0
  
     UPDATE TABLEA 
     SET recordStatus = 1 
     WHERE recordID = @recordID
  
     SELECT *
     FROM TABLEA
     WHERE recordID = @recordID
  END
  </code></pre>
  
  <p>I have tried doing a research, triggers and no luck, hope some one can help. I'm not very proficient at SQL.</p>
  
  <p>Thank you.</p>