USP_SEARCHLIST_FAFEVENTGROUP

CREATE procedure dbo.USP_SEARCHLIST_FAFEVENTGROUP
(
    @NAME nvarchar(100) = null,
    @TYPECODE int = null,
    @FULLNAME nvarchar(100) = null,
    @STATUSCODE int = null,
  @EVENTID uniqueidentifier = null,
    @MAXROWS smallint = 500,
  @REGISTRANTROLECODE tinyint = null,
  @MATCHEXACTNAME bit = 0
)
  as

  if @MATCHEXACTNAME = 0
      set @NAME = COALESCE(@NAME,'') + '%' ;

  set @FULLNAME = COALESCE(@FULLNAME,'') + '%';
  if @REGISTRANTROLECODE is not null
      SELECT @TYPECODE = CASE @REGISTRANTROLECODE
        when 0 then 0
        when 1 then 2 
        when 2 then 0 
        when 3 then 1 
        when 4 then 2 
          when 5 then 1 
        when 6 then 0 
        when 7 then 2 
        when 8 then 0 
        when 9 then 3
    end

    select top(@MAXROWS)
        T.ID,
        T.NAME,
        TYPE,
        LEADER,
        STATUS
        --,E.NAME as EVENTNAME


    from     
    dbo.TEAMFUNDRAISINGTEAM T 
    join dbo.TEAMEXTENSION TE     ON TE.TEAMFUNDRAISINGTEAMID = T.ID
  left join dbo.EVENT E on TE.EVENTID = E.ID
    left join (select dbo.UDA_BUILDLIST(C.NAME) as LEADER, tftc.TEAMFUNDRAISINGTEAMID from TEAMEXTENSION iTX
                left join TEAMFUNDRAISINGTEAMCAPTAIN tftc on iTX.TEAMFUNDRAISINGTEAMID = tftc.TEAMFUNDRAISINGTEAMID
                left join CONSTITUENT c on tftc.CONSTITUENTID = c.ID
              group by tftc.TEAMFUNDRAISINGTEAMID
                ) TFTCs on TFTCs.TEAMFUNDRAISINGTEAMID = T.ID     
    where
         (T.NAME like @NAME)
     and (TE.TYPECODE = @TYPECODE or @TYPECODE is null)
     and (TE.STATUSCODE = @STATUSCODE or @STATUSCODE is null)
     and (ISNULL(TFTCs.LEADER, '') like @FULLNAME)
     and (TE.EVENTID = @EVENTID or @EVENTID is null)
    order by 
        NAME asc