USP_DATAFORMTEMPLATE_VIEW_INVITEE

The load procedure used by the view dataform template "Invitee Summary View Form"
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NOTYETINVITEDCOUNT	int	INOUT	Not yet invited
@INVITEDWITHNORESPONSECOUNT	int	INOUT	Invited with no response
@REGISTEREDCOUNT	int	INOUT	Registered
@DECLINEDCOUNT	int	INOUT	Declined
@INVITEETOTAL	int	INOUT	Total
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_INVITEE
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @NOTYETINVITEDCOUNT int = null output,
                    @INVITEDWITHNORESPONSECOUNT int = null output,
                    @REGISTEREDCOUNT int = null output,
                    @DECLINEDCOUNT int = null output,
                    @INVITEETOTAL int = null output
                )
                as
                    set nocount on;

                    if exists (select ID from dbo.EVENT where ID = @ID)
                        select
                            @NOTYETINVITEDCOUNT = coalesce(sum([INVITEEPROPERTIES].[NOTYETINVITED]), 0),
                            @INVITEDWITHNORESPONSECOUNT = coalesce(sum([INVITEEPROPERTIES].[INVITEDWITHNORESPONSE]), 0),
                            @REGISTEREDCOUNT = coalesce(sum([INVITEEPROPERTIES].[REGISTERED]), 0),
                            @DECLINEDCOUNT = coalesce(sum([INVITEEPROPERTIES].[DECLINED]), 0),
                            @INVITEETOTAL = count(*),
                            @DATALOADED = 1
                        from
                        (
                            select
                                case
                                    when
                                        INVITEE.INVITATIONSENTON is null
                                        and INVITEE.DECLINED = 0
                                    then
                                        1
                                    else
                                        0
                                end [NOTYETINVITED],
                                case
                                    when
                                        INVITEE.DECLINED = 0
                                        and REGISTRANT.ID is null
                                        and INVITEE.INVITATIONSENTON is not null
                                    then
                                        1
                                    else
                                        0
                                end [INVITEDWITHNORESPONSE],
                                case
                                    when
                                        REGISTRANT.ID is not null
                                    then
                                        1
                                    else
                                        0
                                end [REGISTERED],
                                case
                                    when
                                        INVITEE.DECLINED = 1
                                    then
                                        1
                                    else
                                        0
                                end [DECLINED]
                            from
                                dbo.INVITEE
                                left join dbo.REGISTRANT on
                                    INVITEE.CONSTITUENTID = REGISTRANT.CONSTITUENTID
                                    and @ID = REGISTRANT.EVENTID
                            where
                                INVITEE.EVENTID = @ID
                        ) [INVITEEPROPERTIES];

                    else
                        set @DATALOADED = 0;

                    return 0;