UFN_FUNDRAISINGGROUP_TEAMMEMBERNUMBER

create function dbo.UFN_FUNDRAISINGGROUP_TEAMMEMBERNUMBER
(
    @ID uniqueidentifier
)
returns int
with execute as caller
as begin

    DECLARE @TYPECODE int, @result int

    select @TYPECODE=TYPECODE from dbo.TEAMEXTENSION WHERE TEAMFUNDRAISINGTEAMID = @ID

    if @TYPECODE = 3 --HOUSEHOLD

        set @result = 0;

    if @TYPECODE = 2 --Company

        begin
            set @result = (
                select COUNT(TFTM.ID)
                from dbo.TEAMFUNDRAISINGTEAM TFT
                inner join dbo.TEAMFUNDRAISINGTEAM TFT1 ON TFT1.PARENTTEAMID = TFT.ID 
                inner join dbo.TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID = TFT1.ID and TE.TYPECODE = 1
                inner join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISINGTEAMID = TFT1.ID 
                where TFT.ID =@ID 
            )
        end

    if @TYPECODE = 1 --Team

        begin
            set @result = (
                select COUNT(TFTM.ID)
                from dbo.TEAMFUNDRAISINGTEAM TFT
                inner join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID 
                where TFT.ID =@ID 
            )
        end

    return @result;
end