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
No comments:
Post a Comment