USP_MKTSEGMENTATIONFINDERNUMBER_DELETE

Executes the "Marketing Effort Finder Number: Delete" record operation.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the ID of the record being deleted.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the delete.

Definition

Copy



CREATE procedure dbo.[USP_MKTSEGMENTATIONFINDERNUMBER_DELETE]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @DATATABLE nvarchar(128);
  declare @MIN bigint;
  declare @MAX bigint;
  declare @SQL nvarchar(max);
  declare @ASSIGNED bigint;

  begin try
    select 
      @SEGMENTATIONID = [SEGMENTATIONID],
      @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([SEGMENTATIONID]),
      @MIN = (case when [CHECKDIGIT] = 1 then ([MIN] * 10) else [MIN] end),
      @MAX = (case when [CHECKDIGIT] = 1 then (([MAX] * 10) + 9) else [MAX] end)
    from dbo.[MKTSEGMENTATIONFINDERNUMBER]
    where [ID] = @ID;

    if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLE)
      begin
        set @SQL = 'select @ASSIGNED = count([FINDERNUMBER]) from dbo.[' + @DATATABLE + '] where [FINDERNUMBER] > 0 and [FINDERNUMBER] between @MIN and @MAX';
        exec sp_executesql @SQL, N'@ASSIGNED bigint output, @MIN bigint, @MAX bigint', @ASSIGNED = @ASSIGNED output, @MIN = @MIN, @MAX = @MAX;

        if @ASSIGNED > 0
          begin
            set @SQL = 'if exists(select * from dbo.sysindexes where name = ''UIX_' + @DATATABLE + '_FINDERNUMBER'')' + char(13) +
                       '  drop index [UIX_' + @DATATABLE + '_FINDERNUMBER] on dbo.[' + @DATATABLE + ']';
            exec (@SQL);

            set @SQL = 'alter table dbo.[' + @DATATABLE + '] alter column [FINDERNUMBER] bigint null';
            exec (@SQL);

            set @SQL = 'update dbo.[' + @DATATABLE + '] set [FINDERNUMBER] = null where [FINDERNUMBER] > 0 and [FINDERNUMBER] between @MIN and @MAX';
            exec sp_executesql @SQL, N'@MIN bigint, @MAX bigint', @MIN = @MIN, @MAX = @MAX;

            delete from dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO]
            from dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO] as [CACHE]
            inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [CACHE].[SEGMENTID]
            where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID;
          end
      end

    exec dbo.[USP_MKTSEGMENTATIONFINDERNUMBER_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;