Procedures and Case-When

MySQL Procedures and CASE-WHEN

Overview

• MySQL Procedures::

  • Queries stored on the server that can be executed to perform actions on the database.

Procedures without Parameters

• CREATE PROCEDURE Statement:

  • Syntax:

  CREATE PROCEDURE aProcedureName()
  SELECT customerName, COUNT(*)
  FROM Customers
  JOIN Orders ON Customers.customerNumber = Orders.customerNumber
  GROUP BY Customers.customerNumber;

  • Call Procedure:

  CALL aProcedureName();

Procedures with Input Parameters

• Procedures can accept multiple input parameters.

• Example:

  • Syntax:

  CREATE PROCEDURE countOrdersOfCustomersIn(IN aCountry VARCHAR(10))
  SELECT customerName, COUNT(*)
  FROM Customers
  JOIN Orders USING (customerNumber)
  WHERE Customers.country = aCountry
  GROUP BY Customers.customerNumber;

  • Note: USING works like ON but for matching primary key and foreign key names.

Procedures with Input and Output Parameters

• Procedures can also have both input and output parameters.

• Example:

  • Syntax:

  CREATE PROCEDURE countOrdersOfCustomersInOut(IN aCountry VARCHAR(10), OUT numberOfCustomers INT)
  SELECT COUNT(*) INTO numberOfCustomers
  FROM Customers
  JOIN Orders USING (customerNumber)
  WHERE Customers.country = aCountry;

  • Calling Example:

  CALL countOrdersOfCustomersInOut('France', @numberOfCustomers);
  SELECT @numberOfCustomers;

CASE Statements

Structure of CASE Statements

• Components:

  • Contains WHEN, THEN, and ELSE for conditions and outcomes.

  • Finishes with END AS for naming the new column.

• Example Syntax:

CASE
    WHEN x = 0 THEN 'X'
    WHEN x = 1 THEN 'Y'
    ELSE 'Z'
END AS new_column

Using CASE Statements

• Example Query:

SELECT itemno, itemname, 
CASE 
    WHEN itemtype = 'E' THEN 'Equipment' 
    WHEN itemtype = 'C' THEN 'Clothing' 
    WHEN itemtype = 'N' THEN 'Navigation' 
    WHEN itemtype = 'F' THEN 'Furniture' 
    ELSE 'UNKNOWN' 
END AS 'TYPE OF ITEM' 
FROM item;

CASE Statements with Built-in SELECT

• Can be combined with aggregate functions for complex queries.

• Example:

SELECT 
    SUM(CASE WHEN productCode REGEXP '^S10' THEN quantityOrdered END) AS qty_ordered_S10,
    SUM(CASE WHEN productCode REGEXP '^S12' THEN quantityOrdered END) AS qty_ordered_S12,
    SUM(CASE WHEN productCode NOT REGEXP '^S12' AND productCode NOT REGEXP '^S10' THEN quantityOrdered END) AS qty_ordered_SXX
FROM OrderDetails;

Alternative Approach without CASE Statement

• Aggregate function using UNION to achieve a similar result without using CASE.

• Example:

SELECT 'qty_ordered_S10' as product_prefix, SUM(quantityOrdered)
FROM OrderDetails WHERE productCode REGEXP '^S10'
UNION 
SELECT 'qty_ordered_S12' as product_prefix, SUM(quantityOrdered)
FROM OrderDetails WHERE productCode REGEXP '^S12'
UNION 
SELECT 'qty_ordered_Sxx' as product_prefix, SUM(quantityOrdered)
FROM OrderDetails WHERE productCode NOT REGEXP '^S12' AND productCode NOT REGEXP '^S10';