USP_PATTERNBLOCKMEETING_TIMES_UPDATE

            CREATE procedure [dbo].[USP_PATTERNBLOCKMEETING_TIMES_UPDATE]
            (
              @TIMETABLEDAYID uniqueidentifier,
              @CHANGEAGENTID uniqueidentifier = null
            )
            as begin
                set nocount on;
                declare @TempTbl table (
                   [PATTERNBLOCKMEETINGID] uniqueidentifier,
                   [LENGTHINPERIODS] integer)

                insert into @TempTbl select 
                    [ID],
                    dbo.UFN_PATTERNBLOCKMEETING_GETVALID_LENGTHINPERIODS (PATTERNBLOCKMEETING.TIMETABLEDAYPERIODID, PATTERNBLOCKMEETING.LENGTHINPERIODS) LENGTHINPERIODS
                from
                    PATTERNBLOCKMEETING
                where
                    PATTERNBLOCKMEETING.TIMETABLEDAYID = @TIMETABLEDAYID
                    and dbo.UFN_PATTERNBLOCKMEETING_LENGTHINPERIODS_VALID(PATTERNBLOCKMEETING.TIMETABLEDAYPERIODID,PATTERNBLOCKMEETING.LENGTHINPERIODS) = 0

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

                declare @contextCache varbinary(128);
                set @contextCache = CONTEXT_INFO();
                if not @CHANGEAGENTID is null
                    set CONTEXT_INFO @CHANGEAGENTID

                delete PATTERNBLOCKMEETING
                from
                    PATTERNBLOCKMEETING
                inner join 
                    @TempTbl as temp on temp.PATTERNBLOCKMEETINGID = PATTERNBLOCKMEETING.ID
                where
                    temp.LENGTHINPERIODS = 0

                if not @contextCache is null
                    set CONTEXT_INFO @contextCache   

                declare @CURRENTDATE datetime
                set @CURRENTDATE = getdate()

                update PATTERNBLOCKMEETING
                    set LENGTHINPERIODS = temp.LENGTHINPERIODS,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE 
                from
                    PATTERNBLOCKMEETING
                inner join 
                    @TempTbl as temp on temp.PATTERNBLOCKMEETINGID = PATTERNBLOCKMEETING.ID
                where
                    temp.LENGTHINPERIODS > 0

                update PATTERNBLOCKMEETING
                    set [STARTTIME] = TIMETABLEDAYPERIOD.STARTTIME,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE 
                from 
                    PATTERNBLOCKMEETING
                inner join 
                    TIMETABLEDAYPERIOD on TIMETABLEDAYPERIOD.ID = PATTERNBLOCKMEETING.TIMETABLEDAYPERIODID 
                where 
                    PATTERNBLOCKMEETING.TIMETABLEDAYID = @TIMETABLEDAYID   

                update PATTERNBLOCKMEETING
                    set [ENDTIME] =
                            (select ENDTIME from
                                (
                                select ENDTIME, ROW_NUMBER() OVER (ORDER BY TIMETABLEDAYPERIOD.STARTTIME) as 'ROW_NUMBER'
                                from 
                                    TIMETABLEDAYPERIOD
                                where 
                                    TIMETABLEDAYPERIOD.TIMETABLEDAYID = PATTERNBLOCKMEETING.TIMETABLEDAYID 
                                    and TIMETABLEDAYPERIOD.STARTTIME >= PATTERNBLOCKMEETING.STARTTIME 
                                ) as endrow
                            where 
                                endrow.ROW_NUMBER = PATTERNBLOCKMEETING.LENGTHINPERIODS),
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE 
                from 
                    PATTERNBLOCKMEETING
                where 
                    PATTERNBLOCKMEETING.TIMETABLEDAYID = @TIMETABLEDAYID

                return 0;
            end