UFN_FAF_TEAMEMBERCOUNT_BY_COMPANYID

create function dbo.UFN_FAF_TEAMEMBERCOUNT_BY_COMPANYID(@COMPANYID uniqueidentifier)
 returns integer
 AS
  BEGIN
    declare @TEAMS table(ID int IDENTITY(1,1), TEAMID uniqueidentifier);
    declare @i int = 1
    declare @TOTAL int

    declare @maxteammembers int = 0;
    declare @TEAMID uniqueidentifier
    declare @maxteammemberstmp int = 0;

    INSERT INTO @TEAMS(TEAMID)
    SELECT TEAMID from dbo.UFN_FAF_GETTEAMHIEARACHY(@COMPANYID) 

    SELECT @TOTAL = count(*) from @TEAMS

    WHILE @i <= @TOTAL
    BEGIN

        SET @TEAMID = null;
        SELECT @TEAMID = TEAMID FROM @TEAMS WHERE ID = @i;


        SELECT @maxteammemberstmp = count(*) from dbo.UFN_FAF_TEAMMEMBERLIST_BY_TEAMID(@TEAMID)


        IF @maxteammemberstmp > @maxteammembers
        SET @maxteammembers = @maxteammemberstmp    

         SET @i = @i + 1;

    END
        RETURN @maxteammembers;
END