USP_MKTSEGMENTLIST_ROLLBACKIMPORT

CREATE procedure dbo.[USP_MKTSEGMENTLIST_ROLLBACKIMPORT]
(
  @SEGMENTLISTID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @SEGMENTID uniqueidentifier;
  declare @QUERYVIEWCATALOGID uniqueidentifier;
  declare @STANDARDIDSETID uniqueidentifier;
  declare @DUPLICATEIDSETID uniqueidentifier;
  declare @IDSETRECORDTYPEID uniqueidentifier;

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

    select
      @SEGMENTID = [SEGMENTID],
      @QUERYVIEWCATALOGID = [QUERYVIEWCATALOGID],
      @STANDARDIDSETID = [STANDARDIDSETID],
      @DUPLICATEIDSETID = [DUPLICATEIDSETID],
      @IDSETRECORDTYPEID = [IDSETRECORDTYPEID]
    from [MKTSEGMENTLIST]
    where [ID] = @SEGMENTLISTID;

    --Rollback any existing selections to use the old query view...

    exec dbo.[USP_MKTSEGMENTLIST_UPDATEEXISTINGSELECTIONS] @SEGMENTID, 1, @CHANGEAGENTID;

    --Set all query view and IDSet related values to null so we don't invalidate any foreign key relationships when the queries are deleted.

    --Also reset the min/max record IDs.

    update dbo.[MKTSEGMENTLIST] set
      [QUERYVIEWCATALOGID] = null,
      [STANDARDIDSETID] = null,
      [DUPLICATEIDSETID] = null,
      [IDSETRECORDTYPEID] = null,
      [MINIMUMDATAID] = null,
      [MAXIMUMDATAID] = null,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = getdate()
    where [ID] = @SEGMENTLISTID;


    /***********************************************/
    /* Drop the query view for the list segment... */
    /***********************************************/
    if @QUERYVIEWCATALOGID is not null
      begin
        declare @VIEWNAMEFORQUERY nvarchar(128);

        set @VIEWNAMEFORQUERY = dbo.[UFN_MKTSEGMENTLIST_MAKEVIEWNAME_FORQUERY](@SEGMENTLISTID);

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

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


        /*******************************************************************************/
        /* Cleanup the LoadSpecLog table so it doesn't get cluttered with artifacts... */
        /*******************************************************************************/
        declare @LOADSPECLOGID uniqueidentifier;
        declare LOADSPECLOGCURSOR cursor local fast_forward for
          select [ID]
          from dbo.[LOADSPECLOG]
          where [SPECID] = @QUERYVIEWCATALOGID;

        open LOADSPECLOGCURSOR;
        fetch next from LOADSPECLOGCURSOR into @LOADSPECLOGID;

        while (@@FETCH_STATUS = 0)
        begin
          exec dbo.[USP_LOADSPECLOG_DELETEBYID_WITHCHANGEAGENTID] @LOADSPECLOGID, @CHANGEAGENTID;
          fetch next from LOADSPECLOGCURSOR into @LOADSPECLOGID;
        end

        close LOADSPECLOGCURSOR;
        deallocate LOADSPECLOGCURSOR;
      end


    /****************************************************/
    /* Delete the list segment deduplication process... */
    /****************************************************/
    declare @SEGMENTLISTDEDUPEPROCESSID uniqueidentifier;

    select @SEGMENTLISTDEDUPEPROCESSID = [ID]
    from dbo.[MKTSEGMENTLISTDEDUPEPROCESS]
    where [SEGMENTLISTID] = @SEGMENTLISTID;

    if @SEGMENTLISTDEDUPEPROCESSID is not null
      exec dbo.[USP_MKTSEGMENTLISTDEDUPEPROCESS_DELETEBYID_WITHCHANGEAGENTID] @SEGMENTLISTDEDUPEPROCESSID, @CHANGEAGENTID;


    /********************************************************/
    /* Delete the ID sets and the associated record type... */
    /********************************************************/
    exec dbo.[USP_MKTSEGMENTLIST_DELETEIDSETS] @SEGMENTLISTID, @STANDARDIDSETID, @DUPLICATEIDSETID, @IDSETRECORDTYPEID, @CHANGEAGENTID;


    /**********************************/
    /* Delete the imported records... */
    /**********************************/
    delete from dbo.[MKTSEGMENTLISTDATA]
    where [SEGMENTLISTID] = @SEGMENTLISTID;
  end try

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

  return 0;