USP_EXPORTDEFINITIONSEARCH

CREATE procedure dbo.[USP_EXPORTDEFINITIONSEARCH]
(
  @MAXROWS smallint,
  @NAME nvarchar(100) = null,
  @MAILINGTYPECODE tinyint = null,
  @POSTALTEMPLATENAME nvarchar(100) = null,
  @SEGMENTATIONID uniqueidentifier = null
)
as
  set nocount on;

  select distinct
    [MKTEXPORTDEFINITION].[ID],
    [MKTEXPORTDEFINITION].[NAME],
    [MKTEXPORTDEFINITION].[MAILINGTYPE],
    [MKTEXPORTDEFINITION].[POSTALEXPORT],
    [MKTPOSTALTEMPLATE].[NAME] as [POSTALTEMPLATENAME]
  from dbo.[MKTEXPORTDEFINITION]
  inner join dbo.[MKTEXPORTDEFINITIONOUTPUTFIELD] on [MKTEXPORTDEFINITIONOUTPUTFIELD].[EXPORTDEFINITIONID] = [MKTEXPORTDEFINITION].[ID]
  left outer join dbo.[MKTPOSTALTEMPLATE] on [MKTPOSTALTEMPLATE].[ID] = [MKTEXPORTDEFINITION].[POSTALTEMPLATEID]
  where [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID] is null
    and [MKTEXPORTDEFINITION].[ISSYSTEM] = 0
  and [MKTEXPORTDEFINITION].[NAME] like isnull(@NAME, '') + '%'
  and (@MAILINGTYPECODE is null or @MAILINGTYPECODE = 255 or [MKTEXPORTDEFINITION].[MAILINGTYPECODE] = @MAILINGTYPECODE)
  and isnull([MKTPOSTALTEMPLATE].[NAME], '') like isnull(@POSTALTEMPLATENAME, '') + '%'
  and
  (@SEGMENTATIONID is null or (
    [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] in (select [ID] from dbo.[UFN_MKTEXPORTDEFINITION_GETSPECIALQUERYVIEWIDS]())
    or [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] in 
    (
      select
        case when @MAILINGTYPECODE = 0 -- appeal

             then isnull([MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID], [RS].[QUERYVIEWCATALOGID])
             else [RS].[QUERYVIEWCATALOGID] -- acknowledgement, membership

        end
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) as [RS]
      left outer join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = [RS].[QUERYVIEWCATALOGID]
    )
    or (@MAILINGTYPECODE = 1 and [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] in 
    (
      select 
        [GRS].[QUERYVIEWCATALOGID]
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) as [RS]
      inner join dbo.[MKTGIFTRECORDSOURCE] as [GRS] on [GRS].[ID] = [RS].[QUERYVIEWCATALOGID]
    ))
    or (@MAILINGTYPECODE = 2 and [MKTEXPORTDEFINITIONOUTPUTFIELD].[QUERYVIEWCATALOGID] in 
    (
      select 
        [MRS].[QUERYVIEWCATALOGID]
      from dbo.[UFN_MKTSEGMENTATION_GETDISTINCTRECORDSOURCES](@SEGMENTATIONID) as [RS]
      inner join dbo.[MKTMEMBERSHIPRECORDSOURCE] as [MRS] on [MRS].[ID] = [RS].[QUERYVIEWCATALOGID]
    ))
  ))
  order by [MKTEXPORTDEFINITION].[NAME];

  return 0;