UFN_CONSTITUENTMEMBERSHIP_GETOTHERMEMBERS

            CREATE function dbo.UFN_CONSTITUENTMEMBERSHIP_GETOTHERMEMBERS
      (
        @ID uniqueidentifier
      )
       returns nvarchar(1000)
       with execute as caller
       as begin 
        declare @LISTOFOTHERMEMBERS nvarchar(1000);
        declare @NUMMEMBERSALLOWED smallint;
        declare @MEMBERSHIPID uniqueidentifier;
        declare @CONSTITUENTID uniqueidentifier;
        declare @ADDITIONALADDONMEMBERS bit = 0;

        set @LISTOFOTHERMEMBERS='';

        select @MEMBERSHIPID=MEMBERSHIPID , @CONSTITUENTID=CONSTITUENTID 
        from MEMBER 
        where 
        MEMBER.ID=@ID;

        select @NUMMEMBERSALLOWED=MEMBERSHIPLEVEL.MEMBERSALLOWED
        from dbo.MEMBERSHIPLEVEL
        left join MEMBERSHIP on 
        MEMBERSHIP.MEMBERSHIPLEVELID=MEMBERSHIPLEVEL.ID
        where MEMBERSHIP.ID=@MEMBERSHIPID;        

        if @NUMMEMBERSALLOWED <= 1
            begin
                select
                    @ADDITIONALADDONMEMBERS = 1
                from
                    dbo.MEMBERSHIPADDON
                inner join
                    dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
                where
                    ADDON.ADDONTYPECODE = 1 and
                    MEMBERSHIPADDON.MEMBERSHIPID = @MEMBERSHIPID;
            end

        if @NUMMEMBERSALLOWED > 1 or @ADDITIONALADDONMEMBERS = 1
          begin
            select @LISTOFOTHERMEMBERS=dbo.UDA_BUILDLIST(distinct NF.NAME)
            from dbo.MEMBER
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBER.CONSTITUENTID) NF
            where
              MEMBER.MEMBERSHIPID=@MEMBERSHIPID
            and
              MEMBER.CONSTITUENTID <> @CONSTITUENTID
            and 
              MEMBER.ISDROPPED = 0
          end


         return @LISTOFOTHERMEMBERS   

        end