USP_DATALIST_TEAMNAME

CREATE procedure dbo.USP_DATALIST_TEAMNAME
    (@EVENTID uniqueidentifier, 
        @NAME 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

        ) 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

      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 = ISNULL(@NAME,T.NAME)
                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 [level] > 0 and TEAMID IN 
                        (SELECT TEAMFUNDRAISINGTEAMID FROM dbo.TEAMEXTENSION where EVENTID=@EVENTID and TYPECODE = 1)
                      )
          END

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