USP_EVENTLODGINGOPTIONS_COPY

      create procedure dbo.USP_EVENTLODGINGOPTIONS_COPY
        (
          @SOURCEEVENTID uniqueidentifier,
          @DESTINATIONEVENTID uniqueidentifier,
          @CHANGEAGENTID uniqueidentifier = null
        )
            with execute as caller
            as
                set nocount on;

                -- Cannot copy if the source event does not exist

                if not exists (select ID from dbo.EVENT where ID = @SOURCEEVENTID)
                    raiserror('The source event specified does not exist.',13,1);

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

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

                insert into dbo.EVENTLODGING
                (
                    EVENTID,
          EVENTLODGINGLOCATIONID,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                select
                    @DESTINATIONEVENTID,
                    EVENTLODGINGLOCATIONID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.EVENTLODGING
                where
                    EVENTID = @SOURCEEVENTID;

        declare @EVENTLODGINGLOCATIONID uniqueidentifier
        declare EVENTLODGINGLOCATIONIDCURSOR cursor local fast_forward for
          select EVENTLODGINGLOCATIONID
            from EVENTLODGING
            where EVENTID = @SOURCEEVENTID;

        open EVENTLODGINGLOCATIONIDCURSOR
        fetch next from EVENTLODGINGLOCATIONIDCURSOR into @EVENTLODGINGLOCATIONID

        while @@FETCH_STATUS = 0 
          begin
            insert into dbo.EVENTLODGINGROOM
              (
              ROOMTYPECODEID,
              SLEEPS,
              ALLOCATED,
              ADDEDBYID,
              CHANGEDBYID,
              DATEADDED,
              DATECHANGED,
              EVENTLODGINGID
            )
              select
                ROOMTYPECODEID,
                SLEEPS,
                ALLOCATED,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE,
                (select ID 
                  from EVENTLODGING 
                  where EVENTLODGING.EVENTID = @DESTINATIONEVENTID
                    and EVENTLODGING.EVENTLODGINGLOCATIONID = @EVENTLODGINGLOCATIONID)
                from dbo.EVENTLODGINGROOM
                inner join EVENTLODGING on EVENTLODGING.ID = EVENTLODGINGROOM.EVENTLODGINGID
                where
                  EVENTLODGING.EVENTLODGINGLOCATIONID = @EVENTLODGINGLOCATIONID
                  and EVENTLODGING.EVENTID = @SOURCEEVENTID
            fetch next from EVENTLODGINGLOCATIONIDCURSOR into @EVENTLODGINGLOCATIONID
          end

        close EVENTLODGINGLOCATIONIDCURSOR
        deallocate EVENTLODGINGLOCATIONIDCURSOR

                return 0;