Research Development Research Development - 4 months ago 17
MySQL Question

how to apply if and else if conditon in mysql Stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `apply_Leave_SP`(
in leavetypeid int ,
in empid int,
in reason varchar(100),
in startdate date,
in enddate date,
in startsession int,
in endsession int,
in compoffid int,
in mangerid int

)
BEGIN

declare leavestatus int(10) default 0;
declare optionalyHolidays int(10) default 0;
declare listofholidays int(10) default 0;
declare totalhours int (10) default 0;
declare hours int (10) default 0;
declare satsun int (10) default 0;
declare manger_id int(10) default 0;
declare paidleave int(10) default 0;
declare days int(10) default 0;
declare leaveappliedid int(10) default 0;
declare lossofpay int(10) default 0;
declare casualleave int (10) default 0;
declare sickleave int (10) default 0;


select count(holiday_id) into optionalyHolidays from Parabola.holidays where holiday_type_id=2 and
DATEDIFF(startdate,curdate())>=20;

select count(*) into listofholidays from Parabola.holidays where date between startdate and enddate;

SELECT
COUNT(*) AS total into hours
FROM
( SELECT ADDDATE(startdate, INTERVAL @i:=@i+1 DAY) AS DAY
FROM (
SELECT a.a
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
JOIN (SELECT @i := -1) r1
WHERE
@i < DATEDIFF(enddate, startdate)

) AS dateTable
WHERE WEEKDAY(dateTable.Day) IN (5,6);

SELECT datediff(enddate,startdate) into totalhours;
set hours=(totalhours-listofholidays-satsun)*8;


if(leavetypeid=1)then
set leavestatus=5;
else
set leavestatus=1;
end if;

if(optionalyHolidays>=0) then
set leavestatus=5;
end if;

insert into leave_applied(leave_type_id,hours,employee_id,
created_at,updated_at,start_date,end_date,start_date_session,
end_date_session,reason,
status,reminder_count,personal_calendar_event_id,system_calendar_event_id)

values(leavetypeid,hours,empid,curdate(),curdate(),startdate,enddate,
startsession,endsession,
reason,leavestatus,1,'sdasdas','sadeew');



Select LAST_INSERT_ID() into leaveappliedid ;


if((leaveappliedid=1 or 4) and days >paidleave) then
set lossofpay=days-paidleave;


else if((leaveappliedid=1 or 4) and days> @CL) then
set sickleave=days-@CL;
set casualleave=@CL;


else if((leaveappliedid=1 or 4) and days>@SL) then
set casualleave=days-@SL;
set sickleave=@SL;

else
set mangerid=10;

end if;

end;

insert into leave_actual (leave_applied_id,
leave_type_id,hours,start_date,
end_date,created_at,updated_at) values(leaveappliedid,leavetypeid,
hours,start_date,end_date,curdate(),curdate());


insert into leave_approval (leave_applied_id,
manager_id,status ,created_at,updated_at) values
(leaveappliedid,mangerid,leavestatus,curdate(),curdate());

END


this my code when i try to apply else if condition then there is Syntax Error is coming while only with if there is no Error is coming but i have to apply else if condition please suggest me where am doing wrong it show else if Condition

JPG JPG
Answer

The syntax of if, else if is like this:

IF condition THEN
    ... ...
ELSEIF condition THEN
    ... ...
END IF;

See manual here.

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF