Click here to Skip to main content
15,914,323 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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
Posted

1 solution

Hi Pal, nice store procedure, everything seems right but i think you may be misplacing the end it seems.

Try using notepad++, choose language sql plugin from the language option ,then you will get everything that you want.

Sometimes i used to get this type of error when I correct the end statement then everything will come in my way.

This is not a answer but i suggest you to try this.
I hope this might help you a bit.

Feel free to ask any doubt regarding this issue.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900