USP_SEARCHLIST_FAFREGISTRANT

CREATE procedure dbo.USP_SEARCHLIST_FAFREGISTRANT
(
      @KEYNAME nvarchar(100) = null,
      @FIRSTNAME nvarchar(100) = null,
      @FULLNAME nvarchar(100) = null,
      @LOOKUPID nvarchar(100) = null,
      @INCLUDEINDIVIDUALS bit = 1,
      @INCLUDEGROUP bit = 1,
      @ROLE nvarchar(100) = null,
      @EVENTID uniqueidentifier = null,
      @MAXROWS smallint = 500,
      @EXACTMATCHONLY bit = 0
)
as

    set nocount on

    ;With RegistrantsAndGroups
    as
    (
        select RG.CONSTITUENTID, R.Role, RE.STATUS, RG.DATEADDED, C.NAME, 1 AS ISREGISTRANT, RG.ID
        from dbo.REGISTRANT RG (nolock)
        join dbo.REGISTRANTEXTENSION RE (nolock) ON RG.ID = RE.REGISTRANTID
        join dbo.CONSTITUENT C (nolock) on C.ID = RG.CONSTITUENTID
        join dbo.UFN_REGISTRANT_GETFAFROLE(@EVENTID,null) R ON RG.ID = R.REGISTRANTID 
        where RG.EVENTID = ISNULL(@EVENTID,RG.EVENTID) and @INCLUDEINDIVIDUALS = 1
        and (C.KEYNAME like @KEYNAME + '%' OR @KEYNAME is null)
        and (C.FIRSTNAME like @FIRSTNAME + '%' OR @FIRSTNAME is null)

        union all

        select TX.TEAMCONSTITUENTID AS CONSTITUENTID, TX.Type as Role, TX.STATUS, TX.DATEADDED, TFT.NAME, 0 as ISREGISTRANT, TFT.ID
        from dbo.TEAMEXTENSION TX (nolock)
        join dbo.TEAMFUNDRAISINGTEAM TFT (nolock) on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
        where TX.EVENTID = ISNULL(@EVENTID,TX.EVENTID) and TX.STATUSCODE = 0 and @INCLUDEGROUP = 1  
        and (TFT.NAME like @KEYNAME + '%' OR @KEYNAME is null)
        and @FIRSTNAME is null
    )

    select top(@MAXROWS)
          C.ID,
          C.LOOKUPID,
          case when ISREGISTRANT = 0 and C.NAME <> RG.NAME then RG.NAME + ' (' + C.NAME +')' else C.NAME end as NAME,
          RG.Role,
          RG.DATEADDED,    
          RG.ID as REGISTRANTORGROUPID
    from 
          dbo.CONSTITUENT C (nolock)
          join [RegistrantsAndGroups] RG on C.ID = RG.CONSTITUENTID      
    where (C.LOOKUPID like @LOOKUPID + '%' OR @LOOKUPID is null) 
        and (RG.Role LIKE @ROLE + '%' OR @ROLE is null)
    order by C.NAME asc