USP_DATAFORMTEMPLATE_VIEW_REGISTRANTTRAVEL

The load procedure used by the view dataform template "Registrant Travel View"

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.
@ARRIVALTRAVELTYPE	nvarchar(255)	INOUT	Type
@ARRIVALTRAVELNAME	nvarchar(255)	INOUT	Name
@ARRIVALNUMBER	nvarchar(25)	INOUT	Number
@ARRIVALDATE	datetime	INOUT	Date
@ARRIVALTIME	UDT_HOURMINUTE	INOUT	Time
@DEPARTURETRAVELTYPE	nvarchar(255)	INOUT	Type
@DEPARTURETRAVELNAME	nvarchar(255)	INOUT	Name
@DEPARTURENUMBER	nvarchar(25)	INOUT	Number
@DEPARTUREDATE	datetime	INOUT	Date
@DEPARTURETIME	UDT_HOURMINUTE	INOUT	Time
@REGISTRANTTRAVELID	uniqueidentifier	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REGISTRANTTRAVEL
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ARRIVALTRAVELTYPE nvarchar(255) = null output,
    @ARRIVALTRAVELNAME nvarchar(255) = null output,
    @ARRIVALNUMBER nvarchar(25) = null output,
    @ARRIVALDATE datetime = null output,
    @ARRIVALTIME dbo.UDT_HOURMINUTE = null output,
    @DEPARTURETRAVELTYPE nvarchar(255) = null output,
    @DEPARTURETRAVELNAME nvarchar(255) = null output,
    @DEPARTURENUMBER nvarchar(25) = null output,
    @DEPARTUREDATE datetime = null output,
    @DEPARTURETIME dbo.UDT_HOURMINUTE = null output,
    @REGISTRANTTRAVELID uniqueidentifier = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    declare @REGISTRANTCONSTITUENTID uniqueidentifier = null;
    declare @MAINEVENTID uniqueidentifier = null;
    declare @REGISTRANTCANTRAVEL bit = 0;

    -- Travel information is unique to a constituent and a main event coupling, 
    -- not to a registrant record.
    select
        @REGISTRANTCANTRAVEL = 1,
        @REGISTRANTCONSTITUENTID = REGISTRANT.CONSTITUENTID,
        @MAINEVENTID =    case    
                            when EVENT.MAINEVENTID is null then EVENT.ID
                            else EVENT.MAINEVENTID 
                        end
    from
        dbo.REGISTRANT
    inner join    
        dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
    where
        REGISTRANT.ID = @ID;

    select 
        @DATALOADED = 1,
        @ARRIVALTRAVELTYPE = (select EVENTREGISTRANTTRAVELTYPECODE.DESCRIPTION from dbo.EVENTREGISTRANTTRAVELTYPECODE where EVENTREGISTRANTTRAVELTYPECODE.ID = REGISTRANTTRAVEL.REGISTRANTARRIVALTRAVELTYPECODEID),
        @ARRIVALTRAVELNAME = (select EVENTREGISTRANTTRAVELNAMECODE.DESCRIPTION from dbo.EVENTREGISTRANTTRAVELNAMECODE where EVENTREGISTRANTTRAVELNAMECODE.ID = REGISTRANTTRAVEL.REGISTRANTARRIVALTRAVELNAMECODEID),
        @ARRIVALNUMBER = REGISTRANTTRAVEL.ARRIVALNUMBER,
        @ARRIVALDATE = REGISTRANTTRAVEL.ARRIVALDATE,
        @ARRIVALTIME = REGISTRANTTRAVEL.ARRIVALTIME,
        @DEPARTURETRAVELTYPE = (select EVENTREGISTRANTTRAVELTYPECODE.DESCRIPTION from dbo.EVENTREGISTRANTTRAVELTYPECODE where EVENTREGISTRANTTRAVELTYPECODE.ID = REGISTRANTTRAVEL.REGISTRANTDEPARTURETRAVELTYPECODEID),
        @DEPARTURETRAVELNAME = (select EVENTREGISTRANTTRAVELNAMECODE.DESCRIPTION from dbo.EVENTREGISTRANTTRAVELNAMECODE where EVENTREGISTRANTTRAVELNAMECODE.ID = REGISTRANTTRAVEL.REGISTRANTDEPARTURETRAVELNAMECODEID),
        @DEPARTURENUMBER = REGISTRANTTRAVEL.DEPARTURENUMBER,
        @DEPARTUREDATE = REGISTRANTTRAVEL.DEPARTUREDATE,
        @DEPARTURETIME = REGISTRANTTRAVEL.DEPARTURETIME,
        @REGISTRANTTRAVELID = ID
    from 
        dbo.REGISTRANTTRAVEL
    where 
        REGISTRANTTRAVEL.REGISTRANTCONSTITUENTID = @REGISTRANTCONSTITUENTID
        and
        REGISTRANTTRAVEL.MAINEVENTID = @MAINEVENTID;

    -- Nulls are acceptable if the registrant can have travel information, 
    -- but doesn't have a record for it yet.
    if @DATALOADED = 0 and @REGISTRANTCANTRAVEL = 1
        set @DATALOADED = 1;