UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT

CREATE function dbo.UFN_FAFEVENT_GETGROUPNAMEBYCONSTITUENT(
    @CONSTITUENTID uniqueidentifier = null
)
returns nvarchar(200)
with execute as caller
as begin
    DECLARE @RETGROUPNAME nvarchar(200) = ''
    DECLARE @PARENTTEAMID uniqueidentifier = null

    DECLARE  @TEMPTOPGROUP AS TABLE (id int, TopGroupID uniqueidentifier,TopGroupName nvarchar(250))
    DECLARE  @TempTopGroupID uniqueidentifier = null
    DECLARE  @TempTopGroupName nvarchar(250) = null
    DECLARE  @TempCounter integer = 0
    DECLARE  @TempTotalCount integer = 0

    IF (@CONSTITUENTID IS NOT NULL)
    BEGIN    
            --SELECT DISTINCT @TempTopGroupID=TFT.PARENTTEAMID

            SELECT DISTINCT @TempTopGroupID=ISNULL(TFT.PARENTTEAMID, TFT.ID)
            FROM REGISTRANT REG 
            INNER JOIN TEAMFUNDRAISER TFR ON REG.CONSTITUENTID = TFR.CONSTITUENTID 
            INNER JOIN TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISERID=TFR.ID
            INNER JOIN TEAMFUNDRAISINGTEAM TFT ON TFT.ID=TFTM.TEAMFUNDRAISINGTEAMID
            LEFT JOIN  TEAMEXTENSION TE ON TE.TEAMFUNDRAISINGTEAMID = TFT.ID
            WHERE REG.CONSTITUENTID=ISNULL(@CONSTITUENTID,REG.CONSTITUENTID)
            OR TE.TEAMCONSTITUENTID=ISNULL(@CONSTITUENTID,TE.TEAMCONSTITUENTID)

            --First Insert        

            SELECT @TempTopGroupName=ISNULL(NAME,'')
            FROM TEAMFUNDRAISINGTEAM WHERE ID=@TempTopGroupID

            INSERT INTO @TEMPTOPGROUP(id, TopGroupID, TopGroupName)
            VALUES(@TempCounter,@TempTopGroupID,@TempTopGroupName)        

            If EXISTS(SELECT TOP 1 TopGroupID FROM @TEMPTOPGROUP)
            BEGIN
                SELECT @TempCounter = 0, @TempTotalCount = COUNT(id) FROM @TEMPTOPGROUP
                --Loop through to find the very top one

                WHILE (@TempCounter <= @TempTotalCount)
                BEGIN
                    DECLARE @TempCurrentParentTeamID uniqueidentifier = null
                    SELECT Top 1 @TempCurrentParentTeamID=TopGroupID FROM @TEMPTOPGROUP ORDER BY id 

                    SELECT @TempTopGroupID=PARENTTEAMID
                    FROM TEAMFUNDRAISINGTEAM WHERE ID=@TempCurrentParentTeamID

                    SELECT DISTINCT @TempTopGroupID=ID,@TempTopGroupName=ISNULL(NAME,'')        
                    FROM TEAMFUNDRAISINGTEAM WHERE ID=@TempTopGroupID

                    SET @TempCounter = @TempCounter + 1
                    INSERT INTO @TEMPTOPGROUP(id, TopGroupID, TopGroupName)
                    VALUES(@TempCounter,@TempTopGroupID,@TempTopGroupName)        
                END
            END
    END        

    SELECT Top 1 @RETGROUPNAME=TopGroupName FROM @TEMPTOPGROUP ORDER BY id DESC

    return @RETGROUPNAME
end