USP_TRANSACTIONALEMAIL_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
@ISERROR	bit	IN
@ERRORMESSAGE	nvarchar(max)	IN

Definition

Copy



      CREATE procedure dbo.USP_TRANSACTIONALEMAIL_CLEANQUEUE
      (
        @EXTERNALJOBID uniqueidentifier = null,
        @QUEUEID uniqueidentifier = null,
        @STATUS integer,
        @ISERROR bit = 0,
        @ERRORMESSAGE nvarchar(max) = ''
      )
      as
      begin
        set nocount on;

        if @ISERROR = 0
        begin          
          update dbo.EMAILSTATUSTRANSACTIONAL set CLIENTPROCESSENDDATE = getutcdate(), STATUS = @STATUS, EXTERNALJOBIDENTITY = coalesce(@EXTERNALJOBID,EXTERNALJOBIDENTITY)
            where EMAILQUEUETRANSACTIONALID = @QUEUEID;
        end
        else
        begin
          declare @ExceptionXML as xml;

          select @ExceptionXML = STATUSMESSAGE from dbo.EMAILSTATUSTRANSACTIONAL where EMAILQUEUETRANSACTIONALID = @QUEUEID;

          if @ExceptionXML is null
            set @ExceptionXML = '<Errors></Errors>';

          if len((convert(nvarchar(max), @ExceptionXML))) < 1500
          begin
              set @ExceptionXML.modify('insert <Exception Message="{sql:variable("@ERRORMESSAGE")}"/> 
                as last into (/Errors)[1]');
          end

          update dbo.EMAILSTATUSTRANSACTIONAL set CLIENTPROCESSENDDATE = getutcdate(), STATUS = @STATUS, EXTERNALJOBIDENTITY = coalesce(@EXTERNALJOBID,EXTERNALJOBIDENTITY), STATUSMESSAGE = @ExceptionXML, RETRYNUMBER = (case when RETRYNUMBER < 255 then RETRYNUMBER + 1 else RETRYNUMBER end)
            where EMAILQUEUETRANSACTIONALID = @QUEUEID;
        end

        -- delete from queue

          delete from dbo.EMAILQUEUETRANSACTIONAL
            where ID = @QUEUEID;

      end