UFN_MEMBERSHIP_MEMBER_WITHALIASANDNICKNAME

            create function dbo.UFN_MEMBERSHIP_MEMBER_WITHALIASANDNICKNAME(
                @MEMBERSHIPID uniqueidentifier
            )
            returns @MEMBERNAMES table (
                [MEMBERID] uniqueidentifier,
                [CONSTITUENTID] uniqueidentifier,
                [ISPRIMARY] bit,
                [ISDROPPED] bit,
                [ISCAREGIVER] bit,
                [FIRSTNAME] nvarchar(50), --Or nickname, alias

                [KEYNAME] nvarchar(100),
                [HASDUPLICATEACTIVEMEMBERSHIP] bit
            )
            as begin
                declare @MEMBERS table (
                    [MEMBERID] uniqueidentifier,
                    [ISPRIMARY] bit,
                    [ISDROPPED] bit,
                    [ISCAREGIVER] bit,
                    [CONSTITUENTID] uniqueidentifier,
                    [FIRSTNAME] nvarchar(50),
                    [KEYNAME] nvarchar(100),
                    [NICKNAME] nvarchar(50),
                    [HASDUPLICATEACTIVEMEMBERSHIP] bit
                );

                insert into @MEMBERS
                select
                    [MEMBER].[ID] as [MEMBERID],
                    [MEMBER].[ISPRIMARY],
                    [MEMBER].[ISDROPPED],
                    [MEMBER].[ISCAREGIVER],
                    [MEMBER].[CONSTITUENTID],
                    [CONSTITUENT].[FIRSTNAME],
                    [CONSTITUENT].[KEYNAME],
                    [CONSTITUENT].[NICKNAME],
                    [HASANOTHERACTIVEMEMBERSHIPFORPROGRAM].[VALUE] as [HASDUPLICATEACTIVEMEMBERSHIP]
                from dbo.[MEMBER] with (nolock)
                inner join dbo.[CONSTITUENT] with (nolock)
                    on [MEMBER].[CONSTITUENTID] = [CONSTITUENT].[ID]
                inner join dbo.[MEMBERSHIP]
                    on [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
                cross apply (
                    select 
                        case 
                            when exists(
                                select 1 
                                from dbo.[MEMBER] as [MEM] 
                                inner join dbo.[MEMBERSHIP] as [MEMSHIP]
                                    on [MEM].[MEMBERSHIPID] = [MEMSHIP].[ID]
                                where 
                                    [MEM].[CONSTITUENTID] = [MEMBER].[CONSTITUENTID] and
                                    [MEM].[MEMBERSHIPID] <> [MEMBER].[MEMBERSHIPID] and
                                    [MEM].[ISDROPPED] = 0 and
                                    [MEMSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIP].[MEMBERSHIPPROGRAMID]
                            ) 
                                then 1
                            else 0
                        end as [VALUE]
                ) as [HASANOTHERACTIVEMEMBERSHIPFORPROGRAM]
                where [MEMBERSHIP].[ID] = @MEMBERSHIPID;

                insert into @MEMBERNAMES
                select 
                    [MEMBER].[MEMBERID],
                    [MEMBER].[CONSTITUENTID],
                    [MEMBER].[ISPRIMARY],
                    [MEMBER].[ISDROPPED],
                    [MEMBER].[ISCAREGIVER],
                    [MEMBER].[FIRSTNAME],
                    [MEMBER].[KEYNAME],
                    [MEMBER].[HASDUPLICATEACTIVEMEMBERSHIP]
                from @MEMBERS [MEMBER]

                union all
                select 
                    [MEMBER].[MEMBERID],
                    [MEMBER].[CONSTITUENTID],
                    [MEMBER].[ISPRIMARY],
                    [MEMBER].[ISDROPPED],
                    [MEMBER].[ISCAREGIVER],
                    [MEMBER].[NICKNAME],
                    [MEMBER].[KEYNAME],
                    [MEMBER].[HASDUPLICATEACTIVEMEMBERSHIP]
                from @MEMBERS [MEMBER]
                where [MEMBER].[NICKNAME] <> ''

                union all
                select 
                    [MEMBER].[MEMBERID],
                    [MEMBER].[CONSTITUENTID],
                    [MEMBER].[ISPRIMARY],
                    [MEMBER].[ISDROPPED],
                    [MEMBER].[ISCAREGIVER],
                    [ALIAS].[FIRSTNAME],
                    [ALIAS].[KEYNAME],
                    [MEMBER].[HASDUPLICATEACTIVEMEMBERSHIP]
                from @MEMBERS [MEMBER]
                inner join dbo.[ALIAS] with (nolock)
                    on [MEMBER].[CONSTITUENTID] = [ALIAS].[CONSTITUENTID]
                order by [ISDROPPED] asc

                return;
            end