USP_TRANSACTIONALEMAIL_POLLQUEUE

 CREATE procedure dbo.USP_TRANSACTIONALEMAIL_POLLQUEUE
      (
        @THREADID uniqueidentifier,
        @INSTANCEID uniqueidentifier
      )
      as
      begin
          set nocount on;

          declare @QUEUEID table
          (
            ID uniqueidentifier
          )

          -- the select top in the where clause implements a primitive level of chunking in case the transactional jobs get backed up for some reason.

          update dbo.EMAILQUEUETRANSACTIONAL set THREADID = @THREADID
            output inserted.ID into @QUEUEID
            where THREADID = '00000000-0000-0000-0000-000000000000' and ID in (select top 1000 ID from dbo.EMAILQUEUETRANSACTIONAL order by DATEADDEDTOQUEUE desc);

          update dbo.EMAILSTATUSTRANSACTIONAL set INTERNALJOBIDENTITY = @INSTANCEID, CLIENTPROCESSSTARTDATE = isnull(CLIENTPROCESSSTARTDATE, getutcdate())
            where EMAILQUEUETRANSACTIONALID in (select ID from @QUEUEID);


          select E.ID, RECIPIENTDATA, EMAILSTATUSTRANSACTIONAL.UNIQUEIDFIELDNAME, EMAILSTATUSTRANSACTIONAL.EMAILADDRESSFIELDNAME, EMAILSTATUSTRANSACTIONAL.EMAILDISPLAYNAMEFIELDNAME, EMAILSTATUSTRANSACTIONAL.MERGETOKEN, E.EMAILHEADER, CONTENTTEXT, CONTENTHTML, THREADID, SOURCE, E.RETRYNUMBER, DATEADDEDTOQUEUE 
          from dbo.EMAILQUEUETRANSACTIONAL E inner join dbo.EMAILSTATUSTRANSACTIONAL on E.ID = EMAILSTATUSTRANSACTIONAL.EMAILQUEUETRANSACTIONALID
           where THREADID = @THREADID;    
      end