CODEWITHSUNDEEP
mysqlmysql-workbenchmysql-error-1241
boldsinas101
boldsinas101

Reputation: 330

If else in stored procedure in mysql

Hello guys I'm little bit confuse. I just want to ask if this is the right syntax. I try the if else stored proc queries in other posts but its not working.

CREATE PROCEDURE `addBarberCommission`(IN `empID` INT, IN `ioIN` VARCHAR(11))
BEGIN
INSERT INTO `attendance_log` (`emp_id`, `io`) VALUES (`empID`, `ioIN`);
IF (SELECT e.position, e.status, a.io FROM employee e INNER JOIN
attendance_log a WHERE a.emp_id = `empID`
AND e.position = 'Barber'
AND e.status = 'Active'
AND a.io = 'Time In') THEN
INSERT INTO `barber_employee` (`emp_id`, `tot_sal`) VALUES (`empID`, 0);
END IF;
END #

That procedure works but when I call that stored procedure, CALL addBarberCommission(1, 'Time In'); it would produce an

ERROR CODE: 1241. Operand should contain 1 column(s)

I'm just ask for clarification. Thank you

Upvotes: 2

Views: 67

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175766

You need to use EXISTS:

CREATE PROCEDURE `addBarberCommission`(IN `empID` INT, IN `ioIN` VARCHAR(11))
BEGIN
INSERT INTO `attendance_log` (`emp_id`, `io`) VALUES (`empID`, `ioIN`);
IF EXISTS (SELECT e.position, e.status, a.io    --here
           FROM employee e 
           JOIN  attendance_log a 
           WHERE a.emp_id = `empID`
             AND e.position = 'Barber'
             AND e.status = 'Active'
             AND a.io = 'Time In') THEN
  INSERT INTO `barber_employee` (`emp_id`, `tot_sal`) VALUES (`empID`, 0);
END IF;
END #

DBFiddle Demo

Upvotes: 2

Related Questions