USP_GENERALPURPOSEEMAIL_DELETEFROM_MERGEROW

Either deletes row(s) from merge tracking table, or deletes the merge tracking table depending on whether we are at the last chunk.

Parameters

Parameter	Parameter Type	Mode
@QUEUEID	uniqueidentifier	IN
@ISLASTCHUNK	bit	IN
@INSTANCEID	uniqueidentifier	IN

Definition

Copy



      CREATE procedure [dbo].[USP_GENERALPURPOSEEMAIL_DELETEFROM_MERGEROW]
      (
        @QUEUEID uniqueidentifier,
        @ISLASTCHUNK bit,
        @INSTANCEID uniqueidentifier
      )
      with execute as owner
      as
      begin
      set nocount on;
      -- need to ensure that rows are deleted, so deadlock handling is being added

      declare @RETRIES integer = 3;
      while @RETRIES > 0
      begin
        begin try
          begin transaction
              declare @TABLENAME as nvarchar(128);
            declare @DELETIONSQL as nvarchar(max);

            set @TABLENAME = dbo.UFN_GENERALPURPOSEEMAIL_GETDATATABLENAME(@QUEUEID)

            if @ISLASTCHUNK = 1
                  begin
                      declare @MERGEDATASETTABLE nvarchar(128)
                      set @MERGEDATASETTABLE = dbo.UFN_GENERALPURPOSEEMAIL_GETMERGEDATASETTABLENAME(@QUEUEID);

                  set @DELETIONSQL = 'if object_id(''' + @TABLENAME + ''') is not null drop table ' + @TABLENAME + ';'
                  exec sp_executesql @DELETIONSQL;

                  set @DELETIONSQL = 'if object_id(''' + @MERGEDATASETTABLE + ''') is not null drop table ' + @MERGEDATASETTABLE + ';'
                exec sp_executesql @DELETIONSQL;              
                  end
            else
                  begin
                    set @DELETIONSQL = 'if object_id(''' + @TABLENAME + ''') is not null delete ' + @TABLENAME + ' where INSTANCEID = ''' + cast(@INSTANCEID as nvarchar(36)) + ''';'
                      exec sp_executesql @DELETIONSQL;        
                  end

            set @RETRIES = 0

            commit transaction
          end try
          begin catch
            if (ERROR_NUMBER() = 1205)
              set @RETRIES = @RETRIES - 1;
            else
              set @RETRIES = -1;

            if XACT_STATE() <> 0
              rollback transaction

          end catch
        end
    end