USP_UPDATEPACKAGEQUEUE

CREATE procedure BBDW.[USP_UPDATEPACKAGEQUEUE] (@PACKAGE nvarchar(255) = null) as

begin

  --Mark package as completed in the package list
  update BBDW.[PACKAGELIST]
  set [ISCOMPLETE] = 1
  where [PACKAGE] = @PACKAGE;

  --Add any packages to the queue that are now eligible
  insert into BBDW.[PACKAGEQUEUE] with (tablockx) ([PACKAGE], [LASTRUNTIME]) 

  select 
    distinct
    p.[PACKAGE],
    p.[LASTRUNTIME]
  from 
  BBDW.[PACKAGELIST] p
  left join BBDW.[PACKAGEDEPENDENCIES]  d on p.[PACKAGE] = d.[PACKAGE]

  where 
  p.[ISCOMPLETE] = 0
  and p.[LANE] is null
  and (p.[PACKAGE] not in (select [PACKAGE] from BBDW.[PACKAGEDEPENDENCIES] where [DEPENDENCY] in (select [PACKAGE] from BBDW.[PACKAGELIST] where [LANE] is null or [ISCOMPLETE] = 0)))
  and p.[PACKAGE] not in (select [PACKAGE] from BBDW.[PACKAGEQUEUE]);

end

declare @PACKAGEQUEUECOUNT int
declare @PACKAGEPROCESSEDCOUNT int

set @PACKAGEQUEUECOUNT = (select count(1) from BBDW.[PACKAGEQUEUE]);
set @PACKAGEPROCESSEDCOUNT = (select count(1) from BBDW.[PACKAGELIST] where [ISCOMPLETE] = 1);

select
  @PACKAGEQUEUECOUNT as [PACKAGEQUEUECOUNT],
  @PACKAGEPROCESSEDCOUNT as [PACKAGEPROCESSEDCOUNT];