USP_DATALIST_CONSTITUENTREGISTRANTTEAMFUNDRAISINGTEAMS

Displays the team membership records for a registrant on multi-component events.

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Constituent
@EVENTID	uniqueidentifier	IN	Event
@INCLUDESUBEVENTS	bit	IN	Include sub events
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



                create procedure dbo.USP_DATALIST_CONSTITUENTREGISTRANTTEAMFUNDRAISINGTEAMS
                (
                    @CONSTITUENTID uniqueidentifier = null,
                    @EVENTID uniqueidentifier = null,
                    @INCLUDESUBEVENTS bit = 1,
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
                as
                    set nocount on;

                    select
                        [EVENT].[ID] [EVENTID],
                        [TEAMFUNDRAISER].[GOAL],
                        [TEAMFUNDRAISINGTEAMMEMBER].[ID] [TEAMFUNDRAISINGTEAMMEMBERID],
                        [TEAMFUNDRAISINGTEAMMEMBER].[TEAMFUNDRAISINGTEAMID],
                        dbo.UFN_TEAMFUNDRAISINGTEAM_GETNAME([TEAMFUNDRAISINGTEAMMEMBER].[TEAMFUNDRAISINGTEAMID]) [TEAMFUNDRAISINGTEAMNAME],
                        case
                            when [TEAMFUNDRAISINGTEAMCAPTAIN].[ID] is null then 0
                            else 1
                        end [ISTEAMCAPTAIN]
                    from
                        dbo.TEAMFUNDRAISER
                        inner join dbo.EVENT on [TEAMFUNDRAISER].[APPEALID] = [EVENT].[APPEALID]
                        left join dbo.TEAMFUNDRAISINGTEAMMEMBER on [TEAMFUNDRAISER].[ID] = [TEAMFUNDRAISINGTEAMMEMBER].[TEAMFUNDRAISERID]
                        left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN on
                            [TEAMFUNDRAISINGTEAMMEMBER].[TEAMFUNDRAISINGTEAMID] = [TEAMFUNDRAISINGTEAMCAPTAIN].[TEAMFUNDRAISINGTEAMID]
                            and [TEAMFUNDRAISER].[CONSTITUENTID] = [TEAMFUNDRAISINGTEAMCAPTAIN].[CONSTITUENTID]
                    where
                        [TEAMFUNDRAISER].[CONSTITUENTID] = @CONSTITUENTID
                        and
                        (
                            [EVENT].[ID] = @EVENTID
                            or
                            (
                                @INCLUDESUBEVENTS = 1
                                and
                                [EVENT].[MAINEVENTID] = @EVENTID
                            )
                        )
                        and
                        (
                            @CURRENTAPPUSERID is null
                            or
                            dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1
                        )
                    order by
                        [TEAMFUNDRAISINGTEAMMEMBER].[DATECHANGED] desc;