Delete the old orders_update_order stored procedure, and create a new one by executing this code:
-- Drop orders_update_order stored procedure
DROP PROCEDURE orders_update_order$$
-- Create orders_update_order stored procedure
CREATE PROCEDURE orders_update_order(IN inOrderId INT, IN inStatus INT,
IN inComments VARCHAR(255), IN inAuthCode VARCHAR(50),
IN inReference VARCHAR(50))
BEGIN
DECLARE currentStatus INT;
SELECT status
FROM orders
WHERE order_id = inOrderId
INTO currentStatus;
IF inStatus != currentStatus AND (inStatus = 0 OR inStatus = 1) THEN
UPDATE orders SET shipped_on = NULL WHERE order_id = inOrderId;
ELSEIF inStatus != currentStatus AND inStatus = 2 THEN
UPDATE orders SET shipped_on = NOW() WHERE order_id = inOrderId;
END IF;
CHAPTER 17 ?– STORING CUSTOMER ORDERS 549
UPDATE orders
SET status = inStatus, comments = inComments,
auth_code = inAuthCode, reference = inReference
WHERE order_id = inOrderId;
END$$
7. Execute this code, which creates the orders_get_orders_by_customer_id stored procedure:
-- Create orders_get_by_customer_id stored procedure
CREATE PROCEDURE orders_get_by_customer_id(IN inCustomerId INT)
BEGIN
SELECT o.order_id, o.total_amount, o.created_on,
o.shipped_on, o.status, c.name
FROM orders o
INNER JOIN customer c
ON o.customer_id = c.customer_id
WHERE o.customer_id = inCustomerId
ORDER BY o.
Pages:
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661