Wednesday, 19 April 2017

TSQL : ORDER BY in an UPDATE (Queue table example)

Implementing a queue table, I wanted to fetch just one 1 row from a queue table (the next job)

The method below uses one statement, one transaction :)
Mark that row as being dealt with - the UPDATE statement
Fetch all the data about the row - using the OUTPUT clause, rather than a further SELECT
Updates the next desired row in the queue - Using UPDATE against a CTE, which allows you to use ORDER BY

DECLARE @Output TABLE
 (
   id int
  ,procedurename varchar(200)
  ,tablename varchar(200)
  ,databasename varchar(200)

 )

;WITH updateCTE AS (
-- doing an update in a CTE lets you use an ORDER BY clause
 SELECT TOP(1) id, procedurename, tablename, databasename, load_started, spid, load_start
 FROM [ETL].[myLoadQueue] 
 WHERE batch_start = @batchstart
 AND load_started = 0
 ORDER BY id
)

UPDATE updateCTE
SET load_started = 1 , spid = @@SPID, load_start = GETDATE()
OUTPUT deleted.id, deleted.procedurename, deleted.tablename, deleted.databasename INTO @Output