USP_MKTSEGMENTATIONEXCLUSION_DELETEFROMCONSTITUENTSEGMENT

CREATE procedure dbo.USP_MKTSEGMENTATIONEXCLUSION_DELETEFROMCONSTITUENTSEGMENT
(
  @SEGMENTATIONEXCLUSIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @EXCLUSIONTABLE nvarchar(128);
  declare @CONSTITSEGMENTTABLE nvarchar(128);
  declare @SQL nvarchar(max);

  select
    @SEGMENTATIONID = [SEGMENTATIONID],
    @EXCLUSIONTABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME]([SEGMENTATIONID])
  from dbo.[MKTSEGMENTATIONEXCLUSION]
  where [ID] = @SEGMENTATIONEXCLUSIONID;

  declare RECORDSOURCECURSOR cursor local fast_forward for
    select
      dbo.[UFN_CONSTITUENTSEGMENT_MAKETABLENAME]([QUERYVIEWCATALOGID])
    from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID);

  open RECORDSOURCECURSOR;
  fetch next from RECORDSOURCECURSOR into @CONSTITSEGMENTTABLE;

  while (@@FETCH_STATUS = 0)
  begin
    set @SQL = 'delete from dbo.[' + @CONSTITSEGMENTTABLE + ']' + char(13) +
               'from dbo.[' + @CONSTITSEGMENTTABLE + '] as [CS]' + char(13) +
               'inner join dbo.[' + @EXCLUSIONTABLE + '] as [EXCL] on [EXCL].[FINDERNUMBER] = [CS].[FINDERNUMBER]' + char(13) +  
               'where [EXCL].[SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID';
    exec sp_executesql @SQL, N'@SEGMENTATIONEXCLUSIONID uniqueidentifier', @SEGMENTATIONEXCLUSIONID = @SEGMENTATIONEXCLUSIONID;

    fetch next from RECORDSOURCECURSOR into @CONSTITSEGMENTTABLE;
  end

  close RECORDSOURCECURSOR;
  deallocate RECORDSOURCECURSOR;

  return 0;