MySQL Throws an Error IF Function in Stored Procedure

Question

i have some problem regarding my mysql stored procedure, the mysql always shows up error 1064, error mysql syntax. where i was wrong?

   DELIMITER $$

   USE `itin`$$

   DROP PROCEDURE IF EXISTS `sp_Transaction`$$

   CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Transaction`(IN inpt TINYINT(4), IN inpt2 TINYINT(4))
   BEGIN

   IF((inpt = 0) AND (inpt2 = 0)) THEN  
   SELECT idtr_inventory, showStatus(idms_status) AS idms_status, showLocation(idms_location) AS idms_location
, showUnitType(idms_unittype) AS idms_unittype, USER, user_email
, showDepartment(idms_department) AS idms_department, asset_tag, sn, brand, model, showBBUser(bb_user) AS bbUser
, dt_purchase, warranty, dt_warranty_exp, nm_computer, ip_address, remarks
   FROM tr_inventory;

 ELSE IF(inpt = 0) THEN
 SELECT idtr_inventory, showStatus(idms_status) AS idms_status, showLocation(idms_location) AS idms_location
, showUnitType(idms_unittype) AS idms_unittype, USER, user_email
, showDepartment(idms_department) AS idms_department, asset_tag, sn, brand, model, showBBUser(bb_user) AS bbUser
, dt_purchase, warranty, dt_warranty_exp, nm_computer, ip_address, remarks
 FROM tr_inventory WHERE (idms_location = inpt2);

ELSE IF(inpt2 = 0) THEN
SELECT idtr_inventory, showStatus(idms_status) AS idms_status, showLocation(idms_location) AS idms_location
, showUnitType(idms_unittype) AS idms_unittype, USER, user_email
, showDepartment(idms_department) AS idms_department, asset_tag, sn, brand, model, showBBUser(bb_user) AS bbUser
, dt_purchase, warranty, dt_warranty_exp, nm_computer, ip_address, remarks
FROM tr_inventory WHERE (idms_unittype = inpt)

ELSE 
SELECT idtr_inventory, showStatus(idms_status) AS idms_status, showLocation(idms_location) AS idms_location
, showUnitType(idms_unittype) AS idms_unittype, USER, user_email
, showDepartment(idms_department) AS idms_department, asset_tag, sn, brand, model, showBBUser(bb_user) AS bbUser
, dt_purchase, warranty, dt_warranty_exp, nm_computer, ip_address, remarks
FROM tr_inventory WHERE (idms_unittype = inpt) AND (idms_location = inpt2);

END IF;
END IF;
END IF;

END$$

DELIMITER ;

is it wrong in the end if statement? if yes, where i need to place? thanks.

error: Query : CREATE DEFINER=root@localhost PROCEDURE sp_Transaction(in inpt tinyint(4), IN inpt2 TINYINT(4)) begin if((inpt = 0) AND... Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'else

We are unable to recognize that which ELSE is for which IF.

Code Lღver
– Code Lღver

2013-07-12 10:37:26 +00:00
Commented Jul 12, 2013 at 10:37 — Code Lღver
– Code Lღver, Commented Jul 12, 2013 at 10:37
Please add the complete error message

juergen d
– juergen d

2013-07-12 10:37:33 +00:00
Commented Jul 12, 2013 at 10:37 — juergen d
– juergen d, Commented Jul 12, 2013 at 10:37

juergen d · Accepted Answer · 2013-07-12 10:39:22Z

1

You missed a;

...
FROM tr_inventory WHERE (idms_unittype = inpt);
                                              ^------here
ELSE 
...

answered Jul 12, 2013 at 10:39

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Throws an Error IF Function in Stored Procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related