How to use MySQL "WITH AS" syntax with table returned by a stored procedure?

Question

While learning MySQL from an online course I came across this excercise (more or less, I simplify it here):

Task 1: Optimize the following query with use of common table expressions (CTE).

SELECT CONCAT("Cl1: ", COUNT(OrderID), "orders") AS "Total" 
FROM Orders WHERE YEAR(Date) = 2022 AND ClientID = "Cl1" 
UNION 
SELECT CONCAT("Cl2: ", COUNT(OrderID), "orders") 
FROM Orders WHERE YEAR(Date) = 2022 AND ClientID = "Cl2";

And this is their solution (which I understand):

WITH 
CL1_Orders AS (SELECT CONCAT("Cl1: ", COUNT(OrderID), "orders") AS "Total number of orders"  
FROM Orders WHERE YEAR(Date) = 2022 AND ClientID = "Cl1"), 
CL2_Orders AS (SELECT  CONCAT("Cl2: ", COUNT(OrderID), "orders") 
FROM Orders WHERE YEAR(Date) = 2022 AND ClientID = "Cl2")
SELECT * FROM CL1_Orders UNION SELECT * FROM CL2_Orders;

As in previous section of the course I played with stored procedures, I wanted to modify the solution and make use of them too. I wrote the following code:

DELIMITER //
CREATE PROCEDURE totalOrders (IN client VARCHAR(10))
BEGIN
    SELECT CONCAT(client, ": ", COUNT(OrderID), "orders") 
    FROM Orders 
    WHERE YEAR(Date) = 2022 AND ClientID = client;
END//

DELIMITER ;
WITH 
SELECT * FROM CALL totalOrders("Cl1") AS cl1,
SELECT * FROM CALL totalOrders("Cl2") AS cl2
SELECT * FROM cl1 UNION SELECT * FROM cl2;

and executing it results in syntax error ERROR 1064 (42000): 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 'SELECT * FROM CALL totalOrders("Cl1") AS cl1, SELECT * FROM CALL totalOrders("Cl' at line 2

How can I fix it? What is wrong?

Answer 1

MySQL has no return type TABLE, so you need to change your approach

like so with a temporary table

Also this and your query would profit with a index on (YEAR(Date), ClientID )

FYI: neve ever name variable with column names this every database has poblems to determine what you mean, simpler is to have a p_ before, that helps you reqad the code better and the database has a chance to give you the right answer

CREATE tABLE Orders (OrderID int, client varchar(10),ClientID int,  Date date) 

INSERT INTO Orders VALUES ( 1,'test1',1,NOW() - INTERVAL 2 YEAR);
INSERT INTO Orders VALUES ( 2,'test2',2,NOW() - INTERVAL 2 YEAR)

CREATE Temporary table t_temop (Orderclinet varchar(100)) 

CREATE PROCEDURE totalOrders (IN p_client VARCHAR(10))
BEGIN
     INSERT INTO t_temop
    SELECT CONCAT(MAX(client), ": ", COUNT(OrderID), " order(s)") 
    FROM Orders 
    WHERE YEAR(Date) = 2022 AND ClientID = p_client;
END

CALL totalOrders(1);
  CALL totalOrders(2);
SELECT * FROM t_temop

Orderclinet
test1: 1 order(s)
test2: 1 order(s)

fiddle