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.
x
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
Advertisement
Answer
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