Delete the old orders_get_orders_between_dates stored procedure, and create a new one by executing
this code:
-- Drop orders_get_orders_between_dates stored procedure
DROP PROCEDURE orders_get_orders_between_dates$$
-- Create orders_get_orders_between_dates stored procedure
CREATE PROCEDURE orders_get_orders_between_dates(
IN inStartDate DATETIME, IN inEndDate DATETIME)
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.created_on >= inStartDate AND o.created_on <= inEndDate
ORDER BY o.created_on DESC;
END$$
4. Delete the old orders_get_orders_by_status stored procedure, and create a new one by executing
this code:
-- Drop orders_get_orders_by_status stored procedure
DROP PROCEDURE orders_get_orders_by_status$$
CHAPTER 17 ?– STORING CUSTOMER ORDERS 548
-- Create orders_get_orders_by_status stored procedure
CREATE PROCEDURE orders_get_orders_by_status(IN inStatus 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.status = inStatus
ORDER BY o.created_on DESC;
END$$
5. Delete the old orders_get_order_info stored procedure, and create a new one by executing this code:
-- Drop orders_get_order_info stored procedure
DROP PROCEDURE orders_get_order_info$$
-- Create orders_get_order_info stored procedure
CREATE PROCEDURE orders_get_order_info(IN inOrderId INT)
BEGIN
SELECT order_id, total_amount, created_on, shipped_on, status,
comments, customer_id, auth_code, reference
FROM orders
WHERE order_id = inOrderId;
END$$
6.
Pages:
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660