SQL Server存储过程选择top 1并在一条语句中更新?

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:

我尝试用recordStatus=0来选择top 1记录，同时在存储过程中将recordStatus列更新为1，我的问题是可以在一个select语句中完成，还是必须使用3个语句?，这是我得到的:

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.

我已经做了一个研究，没有触发和运气，希望有人能帮助。我不太精通SQL。

Thank you.

谢谢你！

2 个解决方案

#1

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.

假设您正在使用该表作为队列，那么当前代码中没有任何东西可以阻止两个同时运行SELECT和接收相同@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.

您当前的代码没有ORDER BY。这意味着它是任意的，将被更新。如果您根本不关心(堆队列)，那么您可以使用。

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

如果你这样做了，事实上，你需要一个订单，然后你可以使用CTE

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

#2

update top (1) TABLEA set recordStatus = 1 where recordStatus = 0

#1