USP_MKTSEGMENTATION_NEEDSMATCHBACK

CREATE procedure dbo.[USP_MKTSEGMENTATION_NEEDSMATCHBACK]
(
  @SEGMENTATIONID uniqueidentifier,
  @NEEDSMATCHBACK bit = null output
)
as
  set nocount on;

  -- public media marketing efforts always require matchback

  select
    @NEEDSMATCHBACK = case when [MAILINGTYPECODE] = 4 then 1 else 0 end
  from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

  if @NEEDSMATCHBACK = 0
    begin
      declare @SQL nvarchar(max);

      --Check if the mailing has any imported list segments (including imported records in the consolidated list) that need matchback...

      set @SQL = 'select @NEEDSMATCHBACK = case when exists(' + char(13) +
                 '  select top 1 1' + char(13) +
                 '  from dbo.[' + dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID) + '] as [DONORS]' + char(13) +
                 '  inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = [DONORS].[DONORQUERYVIEWCATALOGID])' + char(13) +
                 'then 1 else 0 end';
      exec sp_executesql @SQL, N'@NEEDSMATCHBACK bit output', @NEEDSMATCHBACK = @NEEDSMATCHBACK output;
    end

  if @NEEDSMATCHBACK = 0
    begin
      --Check if the mailing has any non-imported list segments that need matchback...

      select @NEEDSMATCHBACK = (case when exists(
        select top 1 1
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
        where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
        and [MKTSEGMENT].[SEGMENTTYPECODE] = 2)
      then 1 else 0 end);
    end

  return 0;