USP_GENERALPURPOSEEMAIL_CLEANQUEUE

This will update the status in the status table and delete the record from the queue.

Parameters

Parameter	Parameter Type	Mode
@EXTERNALJOBID	uniqueidentifier	IN
@QUEUEID	uniqueidentifier	IN
@STATUS	int	IN
@PROCESSINGID	uniqueidentifier	IN

Definition

Copy



      CREATE procedure [dbo].[USP_GENERALPURPOSEEMAIL_CLEANQUEUE]
      (
        @EXTERNALJOBID uniqueidentifier = null,
        @QUEUEID uniqueidentifier = null,
        @STATUS integer,
        @PROCESSINGID uniqueidentifier
      )
      with execute as owner
      as
      begin
        set nocount on;

               declare @STATUSID uniqueidentifier;
        set @STATUSID = (select top 1 ID from dbo.EMAILSTATUSGENERALPURPOSE where EMAILQUEUEGENERALPURPOSEID = @QUEUEID);
                -- delete from queue

        delete from dbo.EMAILQUEUEGENERALPURPOSE
          where ID = @QUEUEID;

        -- update processing end time

        update dbo.EMAILSTATUSGENERALPURPOSE set CLIENTPROCESSENDDATE = getutcdate(), STATUS = @STATUS, PRIORITY = 0
          where ID = @STATUSID;

          -- subtract 1 from all priorities where priority > 0.  this is because job has been removed.

          update dbo.EMAILSTATUSGENERALPURPOSE set PRIORITY = PRIORITY - 1
            where EMAILQUEUEGENERALPURPOSEID is not null and CLIENTPROCESSENDDATE is null and PRIORITY > 0;


          -- we now need to cleanup the merge table

          declare @MERGESQL as nvarchar(255)
          declare @MERGETABLENAME as nvarchar(305)

          set @MERGETABLENAME = dbo.UFN_GENERALPURPOSEEMAIL_GETDATATABLENAME(@QUEUEID)
          set @MERGESQL = 'if object_id(''' + @MERGETABLENAME + ''') is not null drop table ' + @MERGETABLENAME + ';'
          exec sp_executesql @MERGESQL;

          declare @MERGEDATASETTABLE as nvarchar(128)
          set @MERGEDATASETTABLE = dbo.UFN_GENERALPURPOSEEMAIL_GETMERGEDATASETTABLENAME(@QUEUEID)
          set @MERGESQL = 'if object_id(''' + @MERGEDATASETTABLE + ''') is not null drop table ' + @MERGEDATASETTABLE + ';'
          exec sp_executesql @MERGESQL;

      end