user2837480 user2837480 - 6 months ago 16
MySQL Question

Nested IF ELSE in MySQL

I want to execute below stored procedure, but it gives me an error. Inside flag 1 after updating the order table I want to check another condition and if that's true then I want to run another update query. I tried this in SQL Server with temporary tables and it worked. please help me.

Error is below:


Script line: 4 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 'FROM product PR
INNER JOIN (SELECT PR.ID AS ProductID, (PR.Quantity - OD.Qu' at line 35


DELIMITER $$

DROP PROCEDURE IF EXISTS `onlineshop`.`USP_Public_SaveOrder` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `USP_Public_SaveOrder`(IN pint_Flag INT,INOUT pint_ID BIGINT,IN pint_Status INT,IN pint_CustomerID BIGINT,IN pint_ShippingAddressID BIGINT,IN pint_BillingAddresID BIGINT, IN pdec_ShippingCharge DECIMAL(18,2),IN pdec_Tax DECIMAL(18,2),IN pdec_Total DECIMAL(18,2),IN pdte_TransactionDate DATETIME)
BEGIN
IF(pint_Flag=0) THEN
INSERT INTO orderheader(Status,
CustomerID,
ShippingAddressID,
BillingAddressID,
ShippingCharge,
Tax,
Total,
TransactionDate)
VALUES (pint_Status,
pint_CustomerID,
pint_ShippingAddressID,
pint_BillingAddresID,
pdec_ShippingCharge,
pdec_Tax,
pdec_Total,
pdte_TransactionDate);
SET pint_ID=LAST_INSERT_ID();

ELSEIF(pint_Flag=1) THEN
UPDATE orderheader
SET Status = pint_Status,
CustomerID = pint_CustomerID,
ShippingAddressID = pint_ShippingAddressID,
BillingAddressID = pint_BillingAddresID,
ShippingCharge = pdec_ShippingCharge,
Tax = pdec_Tax,
Total = pdec_Total,
TransactionDate = pdte_TransactionDate
WHERE ID=pint_ID;
IF(pint_Status=2) THEN
UPDATE product SET Quantity = A.remain
FROM product PR
INNER JOIN (SELECT PR.ID AS ProductID, (PR.Quantity - OD.Quantity) AS remain
FROM product P
INNER JOIN orderdetail OD ON OD.ProductID = P.ID AND OD.OrderID = pint_ID) A ON PR.ID = A.ProductID
ELSE
RETURN
END IF;
END IF;
END $$

DELIMITER ;

Answer

Modify your UPDATE block as follows and try it.

IF(pint_Status=2) THEN
    UPDATE product PR
    INNER JOIN (SELECT PR.ID AS ProductID, (PR.Quantity - OD.Quantity) AS remain
                FROM product P
                INNER JOIN orderdetail OD ON OD.ProductID = P.ID AND OD.OrderID = pint_ID
               ) A ON PR.ID = A.ProductID
    SET PR.Quantity = A.remain;
ELSE
    RETURN
END IF;