Schedule a meeting SQL

CREATE proc Sp_meetingcheck_test
@serialno varchar (max) = ",----main file Serialno
@title nvarchar (+) = ',--Meeting Theme
@buser nvarchar (50) = "--File holder
As
DECLARE @Result varchar (max)
DECLARE @sdate datetime
DECLARE @edate datetime
DECLARE @stime datetime
DECLARE @etime datetime
declare @shour nvarchar (50)
declare @ehour nvarchar (50)
DECLARE @sdate1 datetime
DECLARE @edate1 datetime
DECLARE @stime1 datetime
DECLARE @etime1 datetime
declare @shour1 nvarchar (50)
declare @ehour1 nvarchar (50)
declare @adress nvarchar (100)

--insert into A1 (A1) VALUES (' 1 ')

Set XACT_ABORT on
BEGIN TRANSACTION--select * from A1 delete from A1
Begin
Set @Result = ' SUCCESS '

-----------get the current document-related data------
Select
@sdate =meetingdate,
@edate =meetingdate2,
@stime =cast (CONVERT (varchar), meetingdate,23) + "+startdatetime as DateTime",
@etime =cast (CONVERT (varchar), meetingdate2,23) + "+enddatetime as DateTime",
@shour =startdatetime,
@ehour =enddatetime,
@adress =meetingadress
from t_oa_meetingapply where [email protected]

------------compared to the previous 10 sheets, using a cursor to compare-----
Select Top 10
Meetingdate,isnull (meetingdate2,meetingdate) MeetingDate2,
CAST (CONVERT (varchar (), meetingdate,23) + ' +startdatetime as DateTime ' stime1,
CAST (CONVERT (varchar), isnull (meetingdate2,meetingdate), + "+enddatetime as datetime" etime1,
Startdatetime,enddatetime
Into #mttmp1 from t_oa_meetingapply where [email protected]
and checked=1 ORDER BY stime1 Desc
--------------------------------------
DECLARE cur cursor FOR
Select Meetingdate,meetingdate2,stime1,etime1,startdatetime,enddatetime from #mttmp1
For Read Only
Open cur
Fetch cur into @sdate1, @edate1, @stime1, @etime1, @shour1, @ehour1
While @ @fetch_status =0
Begin
--set @Result = ' SUCCESS '
[email protected], @edate1 without time
[email protected], @etime1 with time
[email protected], @ehour1 hour and minute strings like 09:30
if (@[email protected])--date Same
Begin
--if (@[email protected] or @[email protected])
--begin
if (@[email protected] or @[email protected])--Equal time
Begin
Set @Result = ' meeting time is duplicated and cannot be scheduled! '
End
else if ((@stime > @stime1 and @stime < @etime1) and (@etime > @stime1 and @etime < @etime1))--Intermediate time
Begin
Set @Result = ' Meeting time is included, cannot be booked! '
End
else if (@stime < @stime1 and @etime > @etime1)--time on both sides
Begin
Set @Result = ' Meeting time is included, cannot be booked! '
End
else if (@stime < @stime1 and @etime > @stime1)
Begin
Set @Result = ' Meeting time is included, cannot be booked! '
End
else if (@stime < @etime1 and @etime > @etime1)
Begin
Set @Result = ' Meeting time is included, cannot be booked! '
End
else if (@stime < @stime1 and @etime < @stime1) or (@stime > @etime1 and @etime > @etime1))
Begin
Set @Result = ' SUCCESS '
End
Else
Begin
Set @Result = ' SUCCESS '
End

if (@Result <> ' SUCCESS ')
Begin
--return
Break
End
--end
End

Schedule a meeting SQL