USP_DATALIST_FINDERFILECOUNTS

CREATE procedure dbo.[USP_DATALIST_FINDERFILECOUNTS]
(
  @SEGMENTATIONID uniqueidentifier
)
with execute as owner
as
  set nocount on;

  declare @TEMP table ([MAILINGNAME] nvarchar(255), [SEGMENTNAME] nvarchar(255), [SOURCECODE] nvarchar(255), [PACKAGEID] uniqueidentifier, [PACKAGENAME] nvarchar(255), [QUANTITY] int);
  declare @SQL nvarchar(max)
  declare @DATATABLE nvarchar(256);
  declare @MAILINGNAME nvarchar(100);

  set @DATATABLE = dbo.[UFN_MKTFINDERFILE_BUILDTABLENAME](@SEGMENTATIONID);
  select @MAILINGNAME = [NAME] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

  if exists(select 1 from INFORMATION_SCHEMA.TABLES where (TABLE_SCHEMA = 'dbo') and (TABLE_NAME = @DATATABLE))
  begin
    set @SQL = 'select' + char(13) +
               '   @MAILINGNAME [MAILINGNAME],' + char(13) +
               '   isnull([MKTSEGMENTATIONTESTSEGMENT].[NAME], [MKTSEGMENT].[NAME]) [SEGMENTNAME],' + char(13) +
               '   [MKTSOURCECODEMAP].[SOURCECODE] [SOURCECODE],' + char(13) +
               '   isnull([MKTPACKAGE2].[ID], [MKTPACKAGE1].[ID]) [PACKAGEID],' + char(13) +
               '   isnull([MKTPACKAGE2].[NAME], [MKTPACKAGE1].[NAME]) [PACKAGENAME],' + char(13) +
               '   count(*) [QUANTITY]' + char(13) +
               'from dbo.[' + @DATATABLE + '] [DATA]' + char(13) +
               'left join dbo.[MKTSOURCECODEMAP] on [MKTSOURCECODEMAP].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSOURCECODEMAP].[SOURCECODE] = [DATA].[SOURCECODE]' + char(13) +
               'left join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID]' + char(13) +
               'left join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]' + char(13) +
               'left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID]' + char(13) +
               'left join dbo.[MKTPACKAGE] [MKTPACKAGE1] on [MKTPACKAGE1].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]' + char(13) +
               'left join dbo.[MKTPACKAGE] [MKTPACKAGE2] on [MKTPACKAGE2].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]' + char(13) +
               'group by [MKTSEGMENTATIONTESTSEGMENT].[NAME], [MKTSEGMENT].[NAME], [MKTSOURCECODEMAP].[SOURCECODE], [MKTPACKAGE2].[ID], [MKTPACKAGE1].[ID], [MKTPACKAGE2].[NAME], [MKTPACKAGE1].[NAME];';
    insert into @TEMP
      exec sp_executesql @SQL, N'@MAILINGNAME nvarchar(100), @SEGMENTATIONID uniqueidentifier',@MAILINGNAME = @MAILINGNAME, @SEGMENTATIONID = @SEGMENTATIONID;
  end

  if exists (select 1 from @TEMP)
    select * from @TEMP;
  else
    select @MAILINGNAME [MAILINGNAME], null [SEGMENTNAME], null [SOURCECODE], null [PACKAGEID], null [PACKAGETNAME], 0 [QUANTITY]

  return 0;