UFN_EVENTSPEAKER_VALIDDATEANDTIME

Validates the event speaker date and times are valid in the context of the event.
Return

Return Type
bit
Parameters

Parameter	Parameter Type	Mode
@EVENTID	uniqueidentifier	IN
@SPEAKINGDATE	datetime	IN
@SPEAKINGTIME	UDT_HOURMINUTE	IN
Definition

Copy


            CREATE function dbo.UFN_EVENTSPEAKER_VALIDDATEANDTIME
            (
                @EVENTID uniqueidentifier = null,
                @SPEAKINGDATE datetime = null,
                @SPEAKINGTIME UDT_HOURMINUTE = ''
            ) 
            returns bit with execute as caller
            as
            begin            

                if @SPEAKINGDATE is null begin
                    return 1;
                end;

                declare @EVENTSTARTDATE datetime;
                declare @EVENTENDDATE datetime;
                declare @EVENTSTARTTIME UDT_HOURMINUTE;
                declare @EVENTENDTIME UDT_HOURMINUTE;
                declare @EVENTFOUND bit = 0;

                select
                    @EVENTSTARTDATE = STARTDATE, 
                    @EVENTENDDATE = ENDDATE, 
                    @EVENTSTARTTIME = STARTTIME, 
                    @EVENTENDTIME = ENDTIME,
                    @EVENTFOUND = 1
                from
                    dbo.EVENT
                where
                    EVENT.ID = @EVENTID

                if @EVENTFOUND = 1 begin                
                    if @EVENTSTARTTIME <> '' and @SPEAKINGTIME <> '' begin
                        if @EVENTSTARTDATE = @SPEAKINGDATE and @EVENTSTARTTIME > @SPEAKINGTIME begin
                            return 0
                        end;
                    end;

                    if @EVENTENDTIME <> '' and @SPEAKINGTIME <> '' begin
                        if @EVENTENDDATE = @SPEAKINGDATE and @EVENTENDTIME < @SPEAKINGTIME begin
                            return 0
                        end;
                    end;

                    if @EVENTSTARTDATE > @SPEAKINGDATE or @EVENTENDDATE < @SPEAKINGDATE begin
                        return 0;
                    end;

                    return 1;
                end

                return 0;                        
            end