USP_MKTSEGMENTWHITEMAIL_DELETE

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

  declare @IDSETREGISTERID uniqueidentifier;
  declare @OBJECTNAME nvarchar(256);

  if (select isnull(sum([RESPONSES]), 0) from dbo.[MKTSEGMENTWHITEMAILCACHE] where [ID] = @ID) > 0
    begin
      raiserror('BBERR_MKTSEGMENTWHITEMAIL_HASREVENUE', 13, 1);
      raiserror('This white mail segment''s cache shows associated revenue and thus it cannot be deleted.', 1, 11);
      return 1;
    end

  begin try
    select 
      @IDSETREGISTERID = [IDSETREGISTER].[ID],
      @OBJECTNAME = [IDSETREGISTER].[DBOBJECTNAME]
    from dbo.[MKTSEGMENTWHITEMAILREFRESHPROCESS]
    inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTWHITEMAILREFRESHPROCESS].[GIFTIDSETREGISTERID]
    where [MKTSEGMENTWHITEMAILREFRESHPROCESS].[SEGMENTID] = @ID;

    if @IDSETREGISTERID is not null
      begin
        if exists (select top 1 1 from sys.objects where type = 'U' and name = @OBJECTNAME)
          exec (N'drop table dbo.[' + @OBJECTNAME + N']');

        exec dbo.[USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID] @IDSETREGISTERID, @CHANGEAGENTID;
      end

    -- delete the white mail segment record

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

    -- delete the main segment record

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

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

  return 0;