SQL Server stored procedure select top 1 and update in one statement?

Question

I'm trying to select the top 1 record with recordStatus=0 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:

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

I have tried doing a research, triggers and no luck, hope some one can help. I'm not very proficient at SQL.

Thank you.

Solution

Yes it can and should be done in one statement.

You are currently looking up the same row three times. As well as being inefficient this can also cause concurrency problems.

Assuming that you are using the table as a queue there is nothing in your present code that stops two concurrent transactions both running the SELECT and receiving the same @recordID.

Your current code does not have an ORDER BY. That means it is arbitrary which will be updated. If you simply don't care (a heap queue) then you can use.

UPDATE TOP(1) TABLEA
SET    recordStatus = 1
OUTPUT inserted.*
WHERE  recordStatus = 0 

If you do, in fact, require an ORDER BY then you can use a CTE

WITH T AS
(
SELECT TOP (1) recordID
FROM TABLEA
WHERE recordStatus = 0
ORDER BY recordID
)
UPDATE T
SET recordStatus = 1
OUTPUT INSERTED.*

Comments

[m.edmondson]

To be honest I'd be happy to leave it in it's current state. There is little to gain from condensing these statements, and is currently very readable.

[Martin Smith]

@m.edmondson - As well as doing three separate lookups the code in this question has concurrency issues. Two concurrent transactions can both run the SELECT and receive the same @recordID before either one has done the UPDATE to say that recordStatus has changed.

[paparazzo]

In absence of an order by top is non-deterministic. Even if that table as a clustered PK you still need an order by.

[m.edmondson]

@MartinSmith - Am I right in thinking the concurrency issue can be resolved by surrounding it in a transactional block?

[Martin Smith]

@m.edmondson - And using locking hints or appropriate isolation level. At read committed that isn't enough. More efficient and simpler to use OUTPUT

[marc_s]

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all!

[granadaCoder]

Already answered (with "cte"), but you might find a few "extras" at my blog: granadacoder.wordpress.com/2009/07/06/…

[RicEspn]

Thanks Martin, that works just how I wanted, I don't quite understand it yet, but I'm reading about the common table expression (CTE). Also thanks to all of you guys for helping, I hope some day I can return the favor.