USP_GETPACKAGEFROMQUEUE

CREATE procedure [BBDW].[USP_GETPACKAGEFROMQUEUE] (@LANE int) as

  declare @PACKAGETABLE table ([PACKAGE] nvarchar(255));
  declare @PACKAGE nvarchar(255);
  declare @QUEUECOUNT int = 0;

  if exists (select [PACKAGE] from BBDW.[PACKAGEQUEUE])

    begin 

      begin transaction

        declare @LOCKRESULT int;

        exec @LOCKRESULT = sp_getapplock @Resource = 'BBDWETLQUEUE', @LockMode = 'Exclusive', @LockTimeout='60000';  --Timeout is 1 minute

          if @LOCKRESULT >= 0

            begin

              --Pull record from queue and remove it from the queue
              with [QUEUE] as
              (
                select 
                  top 1 [PACKAGE]
                from BBDW.[PACKAGEQUEUE] with (readpast, rowlock, updlock)
                order by [LASTRUNTIME] desc
              )

              delete from [QUEUE] with (readpast, rowlock, updlock)
                output deleted.[PACKAGE] into @PACKAGETABLE;

              set @PACKAGE = (select [PACKAGE] from @PACKAGETABLE);

              --Update lane on packagelist
              update BBDW.[PACKAGELIST]
              set [LANE] = @LANE
              where [PACKAGE] = @PACKAGE;

              set @QUEUECOUNT = (select count(1) from BBDW.[PACKAGEQUEUE]);

              exec @LOCKRESULT = sp_releaseapplock @Resource = 'BBDWETLQUEUE';

            end

      commit transaction

    end

    --Return package pulled from queue and current queue count
    select isnull(@PACKAGE, '') as [PACKAGE], @QUEUECOUNT as [PACKAGEQUEUECOUNT];