Hi friends
The below stored procedure for inserting attendance details and get contact person phone number and message template based on the workingday type
ALTER procedure [dbo].[sp_retreivesmsvalue](@stid int,@currenttime datetime)
as
declare @receiverid int
declare @time datetime
declare @time1 time(7)
declare @date date
set nocount on
begin
--set @time=@currenttime
select @receiverid=Contact_id from Student_Contact_details where Student_id=@stid
SELECT @time= Convert(char(2),DATEPART(hour,@currenttime)) + ':'+convert(char(2),datepart(mi,@currenttime)) + ':' + convert(char(2),datepart(S,@currenttime))
set @time1=CONVERT(time(7),@time)
declare @intime time(7),@outtime time(7)
declare @intimemorning1 time(7),@intimemorning2 time(7)
declare @outtimeevening1 time(7),@outtimeevening2 time(7)
declare @outtimenoon1 time(7),@outtimenoon2 time(7)
declare @workingdaytype varchar(50)
SELECT @date= CONVERT(date,@currenttime,111) --from Workingday_details where Working_date=@currenttime
-- check daytype for attendance
case select @workingdaytype= Workingday_type from Timetemplate_details where Time_id =(select Time_id from Workingday_details where Working_date=@date)
when @workingdaytype='Full Day'
then
set @intimemorning1='00:00:00'
set @intimemorning2='09:40:00'
set @outtimeevening1='16:15:00'
set @outtimeevening2='23:55:00'
begin
-- checking morning times for full day
if @time1 between @intimemorning1 and @intimemorning2
begin
--set @intime=@time
insert into Attendance_details(StudentId,InTime,WorkingdayType,WorkingDate)values(@stid,@time,@workingdaytype,@date)
select m.Receiver_id,t.Template,t.Template_id,k.Phone_number from Message_configuration_details m inner join Message_template_details t on m.Template_id=t.Template_id inner join Student_Contact_details k on m.Receiver_id=k.Contact_id where t.Template_id='201' and m.Receiver_id=@receiverid
end
-- checking evening times for full day
if @time1 between @outtimeevening1 and @outtimeevening2
begin
declare @outtimesforfullday time(7)
set @outtimesforfullday=@time
declare @intim time(7)
select @intim=InTime from Attendance_details where StudentId=@stid
select @intime=In_Time from Timetemplate_details
select @outtime=Out_time from Timetemplate_details
if @intim <@intime and @outtimesforfullday > @outtime
begin
update Attendance_details set OutTime=@time,AttendanceType='FullDayIn' where StudentId=@stid
end
else
begin
update Attendance_details set OutTime=@time,AttendanceType='HalfDaysIn' where StudentId=@stid
end
select m.Receiver_id,t.template,t.Template_id ,k.Phone_number from Message_configuration_details m inner join Message_template_details t on m.Template_id=t.Template_id inner join Student_Contact_details k on m.Receiver_id=k.Contact_id where t.Template_id=202 and m.Receiver_id=@receiverid
end
end
-- case for Half day
when @workingdaytype='Half Day'
then
set @intimemorning1='00:00:00'
set @intimemorning2='09:40:00'
set @outtimeevening1='13:00:00'
set @outtimeevening2='18:00:00'
begin
-- checking morning times for Half day
if @time1 between @intimemorning1 and @intimemorning2
begin
insert into Attendance_details(StudentId,InTime,WorkingdayType,WorkingDate)values(@stid,@time,@workingdaytype,@date)
select m.Receiver_id,t.Template,t.Template_id,k.Phone_number from Message_configuration_details m inner join Message_template_details t on m.Template_id=t.Template_id inner join Student_Contact_details k on m.Receiver_id=k.Contact_id where t.Template_id='201' and m.Receiver_id=@receiverid
end
if @time1 between @outtimeevening1 and @outtimeevening2
declare @outtimeforevening time(7)
set @outtimeforevening=@time1
select @intim=InTime from Attendance_details where StudentId=@stid
select @intime= In_Time from Timetemplate_details
select @outtime=Out_time from Timetemplate_details
begin
-- checking morning times for Half day
if @intim < @intime and @outtimeforevening > @outtime
begin
update Attendance_details set OutTime=@time,AttendanceType='FullDayIn' where StudentId=@stid
end
else
begin
update Attendance_details set OutTime=@time,AttendanceType='HalfDay In' where StudentId=@stid
end
--update Attendance_details set OutTime=@time,AttendanceType= where StudentId=@stid
select m.Receiver_id,t.template,t.Template_id ,k.Phone_number from Message_configuration_details m inner join Message_template_details t on m.Template_id=t.Template_id inner join Student_Contact_details k on m.Receiver_id=k.Contact_id where t.Template_id=202 and m.Receiver_id=@receiverid
end
end
end
The error is
Msg 156, Level 15, State 1, Procedure sp_retreivesmsvalue, Line 20
Incorrect syntax near the keyword 'case'.
Msg 156, Level 15, State 1, Procedure sp_retreivesmsvalue, Line 21
Incorrect syntax near the keyword 'when'.
Msg 156, Level 15, State 1, Procedure sp_retreivesmsvalue, Line 55
Incorrect syntax near the keyword 'when'.
Msg 102, Level 15, State 1, Procedure sp_retreivesmsvalue, Line 97
Incorrect syntax near 'end'.
if i am using if statement for workingdaytype then it works but for full day it also execute half day statement.and if i am using one if statement for full day it return my desired result. please guide me.
Thank you