USP_DATAFORMTEMPLATE_VIEW_REGISTRANTLODGING

The load procedure used by the view dataform template "Registrant Lodging 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.
@LODGINGLOCATION	nvarchar(100)	INOUT	Lodging location
@PHONENUMBER	nvarchar(50)	INOUT	Phone number
@ROOMTYPE	nvarchar(100)	INOUT	Room type
@CAPACITY	int	INOUT	- sleeps
@ROOMNUMBER	nvarchar(10)	INOUT	Room number
@CONFIRMATIONNUMBER	nvarchar(10)	INOUT	Confirmation number
@CHECKINDATE	datetime	INOUT	Check-in date/time
@CHECKINTIME	UDT_HOURMINUTE	INOUT	at
@CHECKOUTDATE	datetime	INOUT	Check-out date/time
@CHECKOUTTIME	UDT_HOURMINUTE	INOUT	at

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REGISTRANTLODGING
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @LODGINGLOCATION nvarchar(100) = null output,
    @PHONENUMBER nvarchar(50) = null output,
    @ROOMTYPE nvarchar(100) = null output,
    @CAPACITY integer = null output,
    @ROOMNUMBER nvarchar(10) = null output,
    @CONFIRMATIONNUMBER nvarchar(10) = null output,
    @CHECKINDATE datetime = null output,
    @CHECKINTIME dbo.UDT_HOURMINUTE = null output,
    @CHECKOUTDATE datetime = null output,
    @CHECKOUTTIME dbo.UDT_HOURMINUTE = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

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

    -- Lodging information is unique to a constituent and a main event coupling, 

    -- not to a registrant record.

    select
        @REGISTRANTCANLODGE = 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,
        @LODGINGLOCATION = EVENTLODGINGLOCATION.NAME,
        @PHONENUMBER = REGISTRANTLODGING.PHONENUMBER,
        @ROOMTYPE = EVENTLODGINGROOMTYPECODE.DESCRIPTION,
        @CAPACITY = EVENTLODGINGROOM.SLEEPS,
        @ROOMNUMBER = EVENTLODGINGROOMINSTANCE.ROOMNUMBER,
        @CONFIRMATIONNUMBER = REGISTRANTLODGING.CONFIRMATIONNUMBER,
        @CHECKINDATE = REGISTRANTLODGING.CHECKINDATE,
        @CHECKINTIME = REGISTRANTLODGING.CHECKINTIME,
        @CHECKOUTDATE = REGISTRANTLODGING.CHECKOUTDATE,
        @CHECKOUTTIME = REGISTRANTLODGING.CHECKOUTTIME
    from 
        dbo.REGISTRANTLODGING
    left join
        dbo.EVENTLODGINGROOMINSTANCE on REGISTRANTLODGING.EVENTLODGINGROOMINSTANCEID = EVENTLODGINGROOMINSTANCE.ID
    left join
        dbo.EVENTLODGINGROOM on EVENTLODGINGROOM.ID = EVENTLODGINGROOMINSTANCE.EVENTLODGINGROOMID
    left join
        dbo.EVENTLODGING on EVENTLODGING.ID = REGISTRANTLODGING.EVENTLODGINGID
    left join
        dbo.EVENTLODGINGLOCATION on EVENTLODGINGLOCATION.ID = EVENTLODGING.EVENTLODGINGLOCATIONID
    left join
        dbo.EVENTLODGINGROOMTYPECODE on EVENTLODGINGROOMTYPECODE.ID = EVENTLODGINGROOM.ROOMTYPECODEID
    where 
        REGISTRANTLODGING.REGISTRANTCONSTITUENTID = @REGISTRANTCONSTITUENTID
        and
        REGISTRANTLODGING.MAINEVENTID = @MAINEVENTID;

    -- Nulls are acceptable if the registrant can have lodging information, 

    -- but doesn't have a record for it yet.

    if @DATALOADED = 0 and @REGISTRANTCANLODGE = 1
        set @DATALOADED = 1;

    return 0;