USP_SEARCHLIST_FAFNFGLOCALFUNDRASINGGROUPS

CREATE procedure dbo.USP_SEARCHLIST_FAFNFGLOCALFUNDRASINGGROUPS
(
  @GROUPNAME nvarchar(100) = null,
  @EVENTID uniqueidentifier = null,
  @LEADER nvarchar(100) = null,
  @ASSIGNEDTOGROUP int = 0,
  @FAFNFGID uniqueidentifier = null,
  @MAXROWS smallint = 500
)
as
  declare @CAMPAIGNID uniqueidentifier;

    set @GROUPNAME = COALESCE(@GROUPNAME,'') + '%' ;
  set @LEADER = '%' + COALESCE(@LEADER,'') + '%' ;

  select @CAMPAIGNID = CAMPAIGNID from dbo.FAFNFGCAMPAIGN where ID = @FAFNFGID

  select TOP(@MAXROWS)
    TFT.ID,
    TFT.NAME as GROUPNAME,
    E.NAME as EVENTNAME,
    TFTCs.LEADER as LEADER,
    TX.TYPE as [ROLE],
    dbo.UFN_NFGLEVEL_GETFULLPATHNAME(LVL.ID, '/', 1) as CURRENTASSIGNMENT
  from TEAMFUNDRAISINGTEAM TFT
    inner join TEAMEXTENSION TX on TX.TEAMFUNDRAISINGTEAMID=TFT.ID
    left join [EVENT] E on E.ID=TX.EVENTID
    left join (select dbo.UDA_BUILDLISTWITHDELIMITER(C.NAME, ', ') as LEADER, iTX.TEAMFUNDRAISINGTEAMID from TEAMEXTENSION iTX
                left join TEAMFUNDRAISINGTEAMCAPTAIN tftc on iTX.TEAMFUNDRAISINGTEAMID = tftc.TEAMFUNDRAISINGTEAMID
                left join CONSTITUENT c on tftc.CONSTITUENTID = c.ID 
                group by iTX.TEAMFUNDRAISINGTEAMID
              ) TFTCs on TFTCs.TEAMFUNDRAISINGTEAMID = TFT.ID
    left join FAFNFGCAMPAIGNLEVEL LVL on LVL.ID=TX.NFGCAMPAIGNLEVELID
    left join FAFNFGCAMPAIGN NFG on NFG.ID = LVL.NFGCAMPAIGNID
  where
    (E.ID in (select EVENTID from EVENTEXTENSION where FAFPROGRAMID = @CAMPAIGNID) or @CAMPAIGNID is null)
    and TFT.NAME LIKE @GROUPNAME
    and TFTCs.LEADER LIKE @LEADER
    and (E.ID = @EVENTID or @EVENTID is null)
    and (
      (@ASSIGNEDTOGROUP = 0 and TX.NFGCAMPAIGNLEVELID is null)
      or
      (@ASSIGNEDTOGROUP = 1 and TX.NFGCAMPAIGNLEVELID is not null)
      or
      (@ASSIGNEDTOGROUP = 2)
    )
    order by TFT.NAME asc