Store procedure with CTE

Help
1

Hi everybody.

I´m trying to create an store procedure with a CTE.

The sp is like this.

DELIMITER //
CREATE OR REPLACE PROCEDURE sp_testcte() AS
BEGIN
WITH cteDates AS (
SELECT distinct YearMonth,
CONCAT(MonthName,’ ',YearData) AS Literal
FROM CustomDates
WHERE CDate Between ‘2020-01-01’ AND ‘2020-12-31’
ORDER BY CDate )
ECHO SELECT * FROM cteDates;
END //
DELIMITER ;

When I try to create it it sends an error.

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 'ECHO SELECT * FROM cteDates’

Does any one have create a sp using CTEs?

2

Try something like this:

DELIMITER //
CREATE OR REPLACE PROCEDURE p2() AS
BEGIN
  echo with cte as (select 2 as x) select x from cte;
END
//
DELIMITER ;

singlestore> call p2();
+---+
| x |
+---+
| 2 |
+---+

The ECHO has to precede a full SELECT statement. In this case, the SELECT statement starts with WITH (to define a CTE).

2 Likes
3

Thanks hanson, it works!