USP_MKTSEGMENTLISTHISTORICAL_PURGE

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

  begin try
    declare @LISTLAYOUTID uniqueidentifier;
    declare @MINIMUMDATAID uniqueidentifier;
    declare @MAXIMUMDATAID uniqueidentifier;
    declare @FIELDNAME nvarchar(128);
    declare @SQL nvarchar(max);
    declare @CURRENTDATE datetime;

    if @CHANGEAGENTID is null
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    select
      @LISTLAYOUTID = [LISTLAYOUTID],
      @MINIMUMDATAID = [MINIMUMDATAID],
      @MAXIMUMDATAID = [MAXIMUMDATAID]
    from dbo.[MKTSEGMENTLIST]
    where [ID] = @ID;


    --Purge all the imported records for this list segment...

    set @SQL = 'update dbo.[MKTSEGMENTLISTDATA] set' + char(13) +
               '  [ISDUPLICATE] = 0,';

    declare LAYOUTFIELDCURSOR cursor local fast_forward for
      select [FIELDNAME]
      from dbo.[MKTLISTLAYOUTFIELD]
      where [LISTLAYOUTID] = @LISTLAYOUTID
      and [MAPPINGCODE] <> 3  --Do not import

      order by [SEQUENCE];

    open LAYOUTFIELDCURSOR;
    fetch next from LAYOUTFIELDCURSOR into @FIELDNAME;

    while (@@FETCH_STATUS = 0)
    begin
      set @SQL = @SQL + char(13) + '  [' + @FIELDNAME + '] = null,';
      fetch next from LAYOUTFIELDCURSOR into @FIELDNAME;
    end

    close LAYOUTFIELDCURSOR;
    deallocate LAYOUTFIELDCURSOR;

    set @SQL = left(@SQL, len(@SQL) - 1) + char(13);

    if @MINIMUMDATAID is not null and @MAXIMUMDATAID is not null
      begin
        set @SQL = @SQL + 'where [ID] between @MINIMUMDATAID and @MAXIMUMDATAID';
        exec sp_executesql @SQL, N'@MINIMUMDATAID uniqueidentifier, @MAXIMUMDATAID uniqueidentifier', @MINIMUMDATAID = @MINIMUMDATAID, @MAXIMUMDATAID = @MAXIMUMDATAID;
      end
    else
      begin
        set @SQL = @SQL + 'where [SEGMENTLISTID] = @ID';
        exec sp_executesql @SQL, N'@ID uniqueidentifier', @ID = @ID;
      end


    --Mark the historical list segment record as purged and set the date purged...

    set @CURRENTDATE = getdate();
    update dbo.[MKTSEGMENTLIST] set
      [STATUSCODE] = 5,  --Purged

      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

    update dbo.[MKTSEGMENTLISTHISTORICAL] set
      [DATEPURGED] = @CURRENTDATE,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;
  end try

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

  return 0;