USP_EVENTLODGINGROOMINSTANCE_ADDORUPDATE

Updates the room type if an instance exists with the given number at the given lodging, else adds the info. Returns the Id of the affected record.
Parameters

Parameter	Parameter Type	Mode
@ROOMNUMBER	nvarchar(10)	IN
@EVENTLODGINGID	uniqueidentifier	IN
@EVENTLODGINGROOMID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTDATE	date	IN
@ID	uniqueidentifier	INOUT
@REGISTRANTLODGINGID	uniqueidentifier	IN
Definition

Copy


CREATE procedure dbo.USP_EVENTLODGINGROOMINSTANCE_ADDORUPDATE
(
    @ROOMNUMBER nvarchar(10),
    @EVENTLODGINGID uniqueidentifier,
    @EVENTLODGINGROOMID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE date = null,
    @ID uniqueidentifier output,
    @REGISTRANTLODGINGID uniqueidentifier = null
)
as

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if @CURRENTDATE is null
        set @CURRENTDATE = getdate();

    -- 6/24/13 Don't put all people with no room number in the same room!

    if @ROOMNUMBER = N''
        select
            @ID = EVENTLODGINGROOMINSTANCEID
        from
            dbo.REGISTRANTLODGING
        where
            ID = @REGISTRANTLODGINGID;
    else
    begin

        declare @EXISTINGEVENTLODGINGROOMID uniqueidentifier;

        select
            @EXISTINGEVENTLODGINGROOMID = EVENTLODGINGROOMID
        from
            dbo.EVENTLODGINGROOMINSTANCE
        where
            EVENTLODGINGROOMINSTANCE.ROOMNUMBER = @ROOMNUMBER
            and EVENTLODGINGROOMINSTANCE.EVENTLODGINGID = @EVENTLODGINGID;

        declare @EXISTINGEVENTLODGINGROOMINSTANCEID uniqueidentifier;
        select
            @EXISTINGEVENTLODGINGROOMINSTANCEID = EVENTLODGINGROOMINSTANCEID
        from
            dbo.REGISTRANTLODGING
        where
            ID = @REGISTRANTLODGINGID;

        select
            @ID = EVENTLODGINGROOMINSTANCE.ID
        from
            dbo.EVENTLODGINGROOMINSTANCE
        where
            EVENTLODGINGROOMINSTANCE.ROOMNUMBER = @ROOMNUMBER
            and EVENTLODGINGROOMINSTANCE.EVENTLODGINGID = @EVENTLODGINGID;

        if @EXISTINGEVENTLODGINGROOMID <> @EVENTLODGINGROOMID --Someone is already in this room number with a different room type

            and (
                @EXISTINGEVENTLODGINGROOMINSTANCEID is null and @ID is not null
                or @EXISTINGEVENTLODGINGROOMINSTANCEID <> @ID -- We aren't changing the room type

            )
        begin
            raiserror('BBERR_EVENTLODGING_ROOMNUMBEREXISTSASOTHERTYPE',13,1);
        end

    end

    if @ID is null
    begin
        set @ID = newid();
        insert into EVENTLODGINGROOMINSTANCE (
            ID,
            ROOMNUMBER,
            EVENTLODGINGID,
            EVENTLODGINGROOMID,
            [ADDEDBYID],
            [CHANGEDBYID],
            [DATEADDED],
            [DATECHANGED]
            )
        values (
            @ID,
            @ROOMNUMBER,
            @EVENTLODGINGID,
            @EVENTLODGINGROOMID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
            )
    end
    else
    begin
        declare @EMPTYGUID uniqueidentifier = cast(cast(0 as binary) as uniqueidentifier);
        update 
            EVENTLODGINGROOMINSTANCE
        set
            EVENTLODGINGROOMINSTANCE.EVENTLODGINGROOMID = @EVENTLODGINGROOMID,
            EVENTLODGINGROOMINSTANCE.ROOMNUMBER = @ROOMNUMBER
        where
            EVENTLODGINGROOMINSTANCE.ID = @ID
            and
            -- Don't rewrite if we don't have to.

            (
                coalesce(EVENTLODGINGROOMINSTANCE.EVENTLODGINGROOMID, @EMPTYGUID) <> coalesce(@EVENTLODGINGROOMID, @EMPTYGUID)
                or EVENTLODGINGROOMINSTANCE.ROOMNUMBER <> @ROOMNUMBER
            );
    end