Stack Exchange
Mysql 8 How to use an if inside a stored procedure and using cte
I am using a CTE inside a stored procedure, and it works ok, but the problem is when I try to add a conditional, I got the following message:

You have an error in your sql ..
  Syntax to use near 'IF. 

CREATE PROCEDURE referrals(LEVEL INT, max_level INT, code VARCHAR(25)) 

WITH RECURSIVE referrals_path (id, NAME, CODE, lvl) AS
 (
SELECT c.id, c.name, c.code, 0 AS lvl
FROM users c
WHERE c.code = code UNION ALL
SELECT c.id, c.name, c.code, cp.lvl + 1
FROM category_path AS cp
JOIN users AS c ON cp.code = c.referred
) 

IF LEVEL > 1 THEN
SELECT * FROM referrals_path; 
ELSE
SELECT * FROM referrals_path WHERE lvl = LEVEL; 

END IF



You cannot use an IF statement in a query. It is meant to execute a block of SQL code depending on a condition, not to be used within a query.
I think, however, that you could rephrase your query as follows:
WITH RECURSIVE referrals_path (id, NAME, CODE, lvl) AS (
    SELECT c.id, c.name, c.code, 0 AS lvl
    FROM users c
    WHERE c.code = code UNION ALL
    SELECT c.id, c.name, c.code, cp.lvl + 1
    FROM category_path AS cp
    JOIN users AS c ON cp.code = c.referred
) 
SELECT * 
FROM referrals_path
WHERE LEVEL > 1 OR lvl = LEVEL 

