Untitled

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_order_customer`(
	IN p_OrderCustomerId INT,
	IN p_StaffID VARCHAR(10),
    IN p_StaffIdDeliver VARCHAR(10),
    IN p_Status VARCHAR(50)
)
BEGIN 
	DECLARE v_Staff varchar(10);
    DECLARE v_StaffDeliver varchar(10);
    
	START TRANSACTION; 
    
    SELECT StaffId INTO v_Staff FROM staff WHERE StaffId = p_StaffID;
    SELECT StaffId INTO v_StaffDeliver FROM staff WHERE StaffId = p_StaffIdDeliver;
    
	IF (v_Staff is not null && v_StaffDeliver is not null) THEN
		UPDATE customerorder
		SET StaffId = p_StaffID, StaffIdDeliver = p_StaffIdDeliver, Status = p_Status
		Where customerorder.CustomerOrderId = p_OrderCustomerId;
		
		IF (p_Status = 'Cancel') THEN
			UPDATE Toy T
			INNER JOIN CustomerOrderDetail COD ON COD.SKU = T.SKU AND COD.CustomerOrderId = p_OrderCustomerId
			SET T.InventoryNumber = T.InventoryNumber + COD.Quantity;
		END IF;
        
		COMMIT;
	ELSE
		ROLLBACK;
    END IF;
	SELECT v_Staff is not null as StaffValid, v_StaffDeliver is not null as StaffDeliverValid;
    
END