USP_MKTSEGMENTLISTHISTORICAL_DELETE

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

  begin try
    declare @RECORDSOURCEID uniqueidentifier;
    declare @MINIMUMDATAID uniqueidentifier;
    declare @MAXIMUMDATAID uniqueidentifier;
    declare @QUERYVIEWCATALOGID uniqueidentifier;
    declare @VIEWNAMEFORQUERY nvarchar(128);
    declare @SQL nvarchar(max);

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

    select
      @RECORDSOURCEID = [MKTLIST].[RECORDSOURCEID],
      @MINIMUMDATAID = [MKTSEGMENTLIST].[MINIMUMDATAID],
      @MAXIMUMDATAID = [MKTSEGMENTLIST].[MAXIMUMDATAID],
      @QUERYVIEWCATALOGID = [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]
    from dbo.[MKTSEGMENTLIST]
    inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
    where [MKTSEGMENTLIST].[ID] = @ID;

    --Delete all the records in the matchback gift donors table.  Since the user cannot change the record source on a list,

    --we only have to worry about deleting from one record source matchback table here.

    set @SQL = 'delete from dbo.[MKTSEGMENTLISTDATADONORS_' + replace(cast(@RECORDSOURCEID as nvarchar(36)), '-', '_') + ']' + char(13) +
               'where [ID] in (select [ID] from dbo.[MKTSEGMENTLISTDATA] where [SEGMENTLISTID] = @ID)';
    exec sp_executesql @SQL, N'@ID uniqueidentifier', @ID = @ID;

    --Delete all the imported records for this list segment, Imported, Historical, Purged, .... , everything...

    if @MINIMUMDATAID is not null and @MAXIMUMDATAID is not null
      delete from dbo.[MKTSEGMENTLISTDATA]
      where [ID] between @MINIMUMDATAID and @MAXIMUMDATAID;
    else
      delete from dbo.[MKTSEGMENTLISTDATA]
      where [SEGMENTLISTID] = @ID;

    --Delete the view left-over from query...

    set @VIEWNAMEFORQUERY = dbo.[UFN_MKTSEGMENTLIST_MAKEVIEWNAME_FORQUERY](@ID);
    if exists(select 1 from INFORMATION_SCHEMA.VIEWS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @VIEWNAMEFORQUERY)
      exec ('drop view dbo.[' + @VIEWNAMEFORQUERY + ']');

    --Delete the historical list segment record...

    exec dbo.[USP_MKTSEGMENTLIST_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;

    --Delete the query view catalog record...

    exec dbo.[USP_QUERYVIEWCATALOG_DELETEBYID_WITHCHANGEAGENTID] @QUERYVIEWCATALOGID, @CHANGEAGENTID;
  end try

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

  return 0;