Research Development Research Development - 1 year ago 36
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 Source

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