USP_DATALIST_TEAMFUNDRAISINGTEAMMEMBER

This returns team fundraisers with their teams for a given appeal.
Parameters

Parameter	Parameter Type	Mode	Description
@APPEALIDTEAMFUNDRAISINGTEAMID	nvarchar(72)	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_TEAMFUNDRAISINGTEAMMEMBER
                (
                    @APPEALIDTEAMFUNDRAISINGTEAMID nvarchar(72) = null
                )
                as
                    set nocount on;

                    declare @APPEALID uniqueidentifier;
                    set @APPEALID = left(@APPEALIDTEAMFUNDRAISINGTEAMID, 36);

                    declare @TEAMFUNDRAISINGTEAMID uniqueidentifier;
                    if len(@APPEALIDTEAMFUNDRAISINGTEAMID) = 72
                        set @TEAMFUNDRAISINGTEAMID = right(@APPEALIDTEAMFUNDRAISINGTEAMID, 36);

                    select
                        TEAMFUNDRAISINGTEAMMEMBER.ID,
                        TEAMFUNDRAISER.ID,
                        TEAMFUNDRAISER.CONSTITUENTID,
                        NF.NAME [SOLICITORNAME],
                        TEAMFUNDRAISER.GOAL,
                        APPEAL.BASECURRENCYID
                    from
                        dbo.TEAMFUNDRAISINGTEAMMEMBER
                        inner join dbo.TEAMFUNDRAISER on TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID = TEAMFUNDRAISER.ID
                        left join dbo.TEAMFUNDRAISINGTEAM on TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.ID
                        left join dbo.APPEAL on APPEAL.ID = TEAMFUNDRAISER.APPEALID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(TEAMFUNDRAISER.CONSTITUENTID) NF
                    where
                        TEAMFUNDRAISER.APPEALID = @APPEALID
                        and
                        (
                            TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID
                            or
                            (
                                TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID is null
                                and
                                @TEAMFUNDRAISINGTEAMID is null
                            )
                        )
                    order by
                        SOLICITORNAME;