USP_SIMPLEDATALIST_PROSPECTPROSPECTPLANFUNDRAISERS

                CREATE procedure dbo.USP_SIMPLEDATALIST_PROSPECTPROSPECTPLANFUNDRAISERS
                (
                    @PROSPECTPLANID uniqueidentifier = null
                )
                as begin
                    set nocount on;

                    -- Using derived table since it needs KEYNAME and FIRSTNAME in the column list for sorting because

                    -- distinct is used but the output columns can only contain the VALUE, LABEL, and DESCRIPTION columns.  

                    -- Using union rather than union all since a fundraiser could be in both prospect manager

                    -- and a plan fundraiser.

                    select
                        ID as VALUE,
                        NAME as LABEL
                    from
                    (
                        select
                            CONSTITUENT.ID,
                            NF.NAME,
                            CONSTITUENT.KEYNAME,
                            CONSTITUENT.FIRSTNAME
                        from dbo.PROSPECTPLAN 
                        inner join dbo.PROSPECT on PROSPECTPLAN.PROSPECTID = PROSPECT.ID
                        inner join dbo.CONSTITUENT on CONSTITUENT.ID = PROSPECT.PROSPECTMANAGERFUNDRAISERID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                        where PROSPECTPLAN.ID = @PROSPECTPLANID

                        union

                        select distinct
                            CONSTITUENT.ID,
                            NF.NAME,
                            CONSTITUENT.KEYNAME,
                            CONSTITUENT.FIRSTNAME
                        from dbo.CONSTITUENT
                        cross apply dbo.UFN_PROSPECTPLAN_FUNDRAISERS(@PROSPECTPLANID) as PLANFUNDRAISERS
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
                        where CONSTITUENT.ID = PLANFUNDRAISERS.ID
                    ) as FUNDRAISERS
                    order by
                            KEYNAME, 
                            FIRSTNAME
                end