USP_MKTSEGMENTATION_CLEARCACHE

CREATE procedure dbo.[USP_MKTSEGMENTATION_CLEARCACHE]
(
  @SEGMENTATIONID uniqueidentifier,
  @QUICKDELETE bit = 0,
  @SKIPADDRESSCACHETABLE bit = 0
)
as
  set nocount on;

  declare @TABLENAME nvarchar(128);
  declare @SQL nvarchar(max);

  declare @LOCKPREFIX nvarchar(50);
  declare @LOCKNAME nvarchar(255);
  declare @LOCKRESULT int;
  declare @LOCKNAMES table([LOCKNAME] nvarchar(255), [SEQUENCE] int);
  declare @RETVAL int;

  begin try
    if @QUICKDELETE is null
      set @QUICKDELETE = 0;

    set @LOCKPREFIX = 'SegmentExclusionCache:';

    /* get an app lock for each segment so that we don't run into problems when this SP gets called while trying to cache values for this segment */
    insert into @LOCKNAMES
      select @LOCKPREFIX + cast([MKTSEGMENTATIONSEGMENT].[ID] as nvarchar(36)), [MKTSEGMENTATIONSEGMENT].[SEQUENCE]
      from dbo.[MKTSEGMENTATIONSEGMENT]
      where [SEGMENTATIONID] = @SEGMENTATIONID;

    declare GETLOCKCURSOR cursor local fast_forward for
      select [LOCKNAME]
      from @LOCKNAMES
      order by [SEQUENCE];

    open GETLOCKCURSOR;
    fetch next from GETLOCKCURSOR into @LOCKNAME;

    while (@@FETCH_STATUS = 0)
    begin
      exec @LOCKRESULT = sp_getapplock @Resource=@LOCKNAME, @LockMode='Exclusive', @LockOwner='Session', @LockTimeout=3600000;
      if @LOCKRESULT < 0
        raiserror('A failure or timeout occurred while requesting an app lock to clear a marketing effort segment''s cache.', 13, 1);
      fetch next from GETLOCKCURSOR into @LOCKNAME;
    end

    close GETLOCKCURSOR;
    deallocate GETLOCKCURSOR;


    /* Delete all the cache info records for this mailing */
    delete from dbo.[MKTSEGMENTATIONSEGMENTCACHEINFO]
    where [SEGMENTID] in (select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID);


    /* Only skip this when needed because we need this table to stay around after activation. */
    if @SKIPADDRESSCACHETABLE = 0
      begin
        /* Delete all the records for this mailing in the address cache table. */
        set @TABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
        if exists(select * from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @TABLENAME)
          begin
            set @SQL = 'truncate table dbo.[' + @TABLENAME + ']';
            exec (@SQL);
          end

        /* Delete all the records for this mailing in the email address cache table. */
        set @TABLENAME = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
        if exists(select * from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @TABLENAME)
          begin
            set @SQL = 'truncate table dbo.[' + @TABLENAME + ']';
            exec (@SQL);
          end
      end
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    set @RETVAL = 1;
  end catch


  /* Release all the app locks */
  declare RELEASELOCKCURSOR cursor local fast_forward for
    select [LOCKNAME]
    from @LOCKNAMES
    order by [SEQUENCE];

  open RELEASELOCKCURSOR;
  fetch next from RELEASELOCKCURSOR into @LOCKNAME;

  while (@@FETCH_STATUS = 0)
  begin
    exec sp_releaseapplock @Resource=@LOCKNAME, @LockOwner='Session';
    fetch next from RELEASELOCKCURSOR into @LOCKNAME;
  end

  close RELEASELOCKCURSOR;
  deallocate RELEASELOCKCURSOR;

  return isnull(@RETVAL, 0);