Blog Archives

SQL Server – Select, Update and Select in a Single statement

I came across a scenario where I had to optimize the stored procedure which was lengthy and using multiple locks to do the required operation. Objective of the stored procedure is to allocate slots based on some conditions.   Operations involved:

  1. Select few top rows based on slot status and some other conditions.
  2. Update its status as allocated.
  3. Return allocated slot ids to the user.

Below is the dress downed version of actual lengthy code.

SELECT TOP 3 * INTO #tempslot FROM dbo.ExamSlot IES WITH (NOLOCK)
WHERE IES.slot_status = 0
ORDER BY 1 DESC


UPDATE dbo.ExamSlot
SET slot_status = 1
FROM dbo.ExamSlot ES WITH (ROWLOCK)
INNER JOIN #tempslot TS ON ES.slot_id = TS.slot_id


SELECT ES.slot_id FROM dbo.ExamSlot ES WITH (ROWLOCK)
INNER JOIN #tempslot TS ON ES.slot_id = TS.slot_id

Below is the single statement to achieve above objective.

UPDATE SLT
SET SLT.slot_status =0
OUTPUT INSERTED.slot_id,
FROM (SELECT TOP 3 * FROM dbo.ExamSlot IES WITH (HOLDLOCK)
WHERE IES.slot_status = 1
ORDER BY 1 DESC) AS SLT