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

Advertisements

About Krishnaraj Barvathaya

I am Krishnaraj Barvathaya, a software developer from Bangalore and I primarily write on Microsoft Technologies and latest technical stuffs which interests me.

Posted on March 14, 2012, in .Net, SQL Server and tagged , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: