USP_EVENT_CHECKSCHEDULECONFLICT

Checks the availability of an event location.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@EVENTLOCATIONID	uniqueidentifier	IN
@EVENTDATE	date	IN
@STARTTIME	datetime	IN
@ENDTIME	datetime	IN
Definition

Copy


            CREATE procedure dbo.USP_EVENT_CHECKSCHEDULECONFLICT
            (
                @ID uniqueidentifier=null output,
                @EVENTLOCATIONID uniqueidentifier=null,
                @EVENTDATE date=null,
                @STARTTIME datetime=null,
                @ENDTIME datetime=null

            )
            as
            set nocount on;
            begin 
                select 
                    @ID = ID 
                from dbo.EVENT 
                where
                    (
                        (
                            STARTDATE = ENDDATE and
                            STARTDATE = @EVENTDATE and
                            (
                                (
                                    @STARTTIME >= STARTDATETIME and 
                                    @STARTTIME < ENDDATETIME
                                ) or 
                                (
                                    @ENDTIME > STARTDATETIME and 
                                    @ENDTIME <= ENDDATETIME
                                ) or
                                (
                                    @STARTTIME > STARTDATETIME and 
                                    @ENDTIME <= ENDDATETIME
                                ) or 
                                (
                                    @STARTTIME <= STARTDATETIME and 
                                    @ENDTIME >= ENDDATETIME
                                )
                            )
                        ) or
                        (
                            STARTDATE < ENDDATE and
                            @EVENTDATE between STARTDATE and ENDDATE and
                            (
                                (
                                    @STARTTIME <= STARTDATETIME and 
                                    @ENDTIME > STARTDATETIME
                                ) or 
                                (
                                    @STARTTIME >= STARTDATETIME and 
                                    @STARTTIME < ENDDATETIME
                                )
                            )
                        )
                    ) and
                    (
                        EVENTLOCATIONID = @EVENTLOCATIONID or
                        exists
                        (
                            select 1 from dbo.PROGRAMEVENTLOCATION 
                            where 
                                PROGRAMEVENTLOCATION.EVENTLOCATIONID = @EVENTLOCATIONID and
                                PROGRAMEVENTLOCATION.EVENTID = EVENT.ID
                        ) 
                    ) and
                    (
                        PROGRAMID is null or
                        exists
                        (
                            select 1 from dbo.PROGRAM
                            where
                                EVENT.PROGRAMID = PROGRAM.ID and
                                PROGRAM.ISACTIVE = 1
                        )
                    )

            end
            return 0;