USP_DATALIST_TEAMNAMESEARCH

CREATE procedure dbo.USP_DATALIST_TEAMNAMESEARCH  
 (@EVENTID uniqueidentifier,   
  @SEARCHNAME varchar(200) = null,   
  @CEVENTID uniqueidentifier = NULL,  
  @ROLETYPE tinyint = NULL, -- 1: Team, 2: Company, 3: Household  

  @DISPLAYONLYACTIVE bit = 1, -- 1: YES, 0: NO,  

  @ID uniqueidentifier = NULL,
  @MINCHARS tinyint = 3 -- set the minimum number of characters to search  


  ) with execute as owner  
as  
 set nocount on;  

  DECLARE @STATUSCODE tinyint  
    DECLARE @TEAMS table  
      (  
        ID uniqueidentifier null,  
        NAME nvarchar(200) null,  
        TEAMCAPTAINLIST varchar(800) null,  
     ISEXISTS bit null,  
        STATUS nvarchar(16) null,  
     MEMBERS integer null,  
     TEAMS integer null,  
        HASPARENT bit null  
        )  

   IF @DISPLAYONLYACTIVE = 1  
   SET @STATUSCODE = 0  

   DECLARE @SEARCHNAMELENGTH integer = LEN(@SEARCHNAME)
   IF @SEARCHNAME <> '' AND @SEARCHNAMELENGTH >= @MINCHARS
      INSERT INTO @TEAMS(ID, NAME, TEAMCAPTAINLIST, ISEXISTS,STATUS,HASPARENT)  
                SELECT   
                T.ID,  
                T.NAME,  
                dbo.UDA_BUILDLIST(C.NAME) TEAMCAPTAINLIST,  
                dbo.UFN_ISTEAMNAMEEXISTS(@CEVENTID, T.NAME) as ISEXISTS,  
                TE.STATUS,  
                CASE  
                  WHEN T.PARENTTEAMID IS NOT NULL THEN 'true'  
                  ELSE 'false'  
                END  
                FROM dbo.TEAMFUNDRAISINGTEAM T WITH (NOLOCK)  
                INNER JOIN dbo.EVENT E WITH (NOLOCK)  
                ON T.APPEALID = E.APPEALID  
                INNER JOIN dbo.TEAMEXTENSION TE (NOLOCK)  
                ON TE.TEAMFUNDRAISINGTEAMID = T.ID  
                AND TE.TYPECODE = ISNULL(@ROLETYPE,TE.TYPECODE)  
                LEFT OUTER JOIN dbo.TEAMFUNDRAISINGTEAMCAPTAIN TC WITH (NOLOCK)  
                ON TC.TEAMFUNDRAISINGTEAMID = T.ID  
                LEFT OUTER JOIN dbo.CONSTITUENT C WITH (NOLOCK)  
                ON C.ID = TC.CONSTITUENTID  
                WHERE E.ID = @EVENTID 
                AND T.NAME LIKE @SEARCHNAME +'%'
                AND TE.STATUSCODE = ISNULL(@STATUSCODE,TE.STATUSCODE)  
                AND T.ID = ISNULL(@ID,T.ID)  
                GROUP BY T.ID, T.NAME, TE.STATUS, T.PARENTTEAMID  
                ORDER BY T.NAME  

        IF @ROLETYPE = 1  
      UPDATE @TEAMS  
      SET MEMBERS = (SELECT COUNT(*) FROM dbo.UFN_FAF_TEAMMEMBERLIST_BY_TEAMID(ID))  

       IF @ROLETYPE = 3  
        UPDATE @TEAMS  
        SET MEMBERS = (SELECT COUNT(*) FROM dbo.UFN_FAF_HOUSEHOLDMEMBERS_BY_HHTEAMID(ID))  

       IF @ROLETYPE = 2  
       BEGIN  
         UPDATE @TEAMS  
        SET MEMBERS = (SELECT COUNT(*) FROM dbo.UFN_FAF_COMPANYINDIVIDUALLIST_BY_COMPANYID(ID))  

        UPDATE @TEAMS  
        SET TEAMS = (SELECT COUNT(*) FROM dbo.UFN_FAF_GETTEAMHIEARACHY(ID) WHERE PARENTTEAMID IN   
                        (SELECT TEAMFUNDRAISINGTEAMID FROM dbo.TEAMEXTENSION where EVENTID=@EVENTID and TYPECODE = 2)  
                      )  
       END  

       SELECT   
          ID,  
          NAME,  
          TEAMCAPTAINLIST,  
          ISEXISTS,  
          STATUS,  
          MEMBERS,  
          TEAMS,  
          HASPARENT  
        FROM @TEAMS  
        ORDER BY NAME