USP_MKTUPDATEMAILINGCOUNTS_GETSELECTIONINFO

CREATE procedure dbo.USP_MKTUPDATEMAILINGCOUNTS_GETSELECTIONINFO
(
  @SEGMENTATIONEXCLUSIONID uniqueidentifier,
  @SELECTIONNAME nvarchar(128),
  @OVERWRITE bit,
  @RUN bit
)
as
  set nocount on;

  declare @TEMP table(
    [QUERYVIEWCATALOGID] uniqueidentifier, 
    [DISPLAYNAME] nvarchar(255), 
    [DESCRIPTION] nvarchar(255), 
    [RECORDTYPEID] uniqueidentifier,
    [PRIMARYKEYTYPENAME] nvarchar(128),
    [IDSETTABLENAME] nvarchar(128))
  declare @SEGMENTATIONID uniqueidentifier;
  declare @EXCLUSIONTABLE nvarchar(128);

  if @RUN = 1
  begin
    select @SEGMENTATIONID = [SEGMENTATIONID] from dbo.[MKTSEGMENTATIONEXCLUSION] where [ID] = @SEGMENTATIONEXCLUSIONID;
    set @EXCLUSIONTABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME](@SEGMENTATIONID);
  end
  else
  begin
    set @SEGMENTATIONID = @SEGMENTATIONEXCLUSIONID;
    set @EXCLUSIONTABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);
  end

  declare @SQL nvarchar(max);
  set @SQL =  'with SOURCES ([QUERYVIEWCATALOGID])' + char(13) +
              'as (' + char(13) +
              '  select distinct [DONORQUERYVIEWCATALOGID]' + char(13) +
              '  from dbo.[' + @EXCLUSIONTABLE + ']' + char(13);
  set @SQL =  @SQL + case when @RUN = 1 then '  where [SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID' + char(13) else '' end;
  set @SQL =  @SQL + ')' + char(13) +
              'select' + char(13) +
              '  [QUERYVIEWCATALOG].[ID] [QUERYVIEWCATALOGID],' + char(13) +
              '  (@SELECTIONNAME + '' ('' + [QUERYVIEWCATALOG].[DISPLAYNAME] + '')'') as [DISPLAYNAME],' + char(13) +
              '  (''Members removed from '' + (select [NAME] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID)) as [DESCRIPTION],' + char(13) +
              '  [QUERYVIEWCATALOG].[RECORDTYPEID],' + char(13) +
              '  [QUERYVIEWCATALOG].[PRIMARYKEYTYPENAME],' + char(13) +
              '  (select [DBOBJECTNAME] from dbo.[IDSETREGISTER] where [NAME] = (@SELECTIONNAME + '' ('' + [QUERYVIEWCATALOG].[DISPLAYNAME] + '')'') and @OVERWRITE = 1) as [IDSETTABLENAME]' + char(13) +
              'from' + char(13) +
              '  dbo.[QUERYVIEWCATALOG]' + char(13) +
              '  inner join [SOURCES] on [SOURCES].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]';
  insert into @TEMP
    exec sp_executesql @SQL, N'@SEGMENTATIONEXCLUSIONID uniqueidentifier, @SEGMENTATIONID uniqueidentifier, @SELECTIONNAME nvarchar(128), @OVERWRITE bit', @SEGMENTATIONEXCLUSIONID = @SEGMENTATIONEXCLUSIONID, @SEGMENTATIONID = @SEGMENTATIONID, @SELECTIONNAME = @SELECTIONNAME, @OVERWRITE = @OVERWRITE;

  select 
    [QUERYVIEWCATALOGID],
    [DISPLAYNAME],
    [DESCRIPTION],
    [RECORDTYPEID],
    [PRIMARYKEYTYPENAME],
    [IDSETTABLENAME]
  from @TEMP;

  return 0;