What's wrong with this stored procedure (nested SELECT)?

Question

This is throwing a syntax error on the first SELECT - why?

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_cand_by_pos_skill`(IN id INT)
BEGIN
SELECT *  FROM candidates 
WHERE cid = ANY(SELECT candid FROM positionskills p, candidate_skills s WHERE p.positionid = id and s.skillid = p.skill);
END

Please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) — Madhur Bhaiya, Nov 09 '18 at 11:27
Did you remember to change the delimiter before you attempted to define the procedure i.e. `DELIMITER // CREATE ...` — Nick, Nov 09 '18 at 11:29
And please use `IN` instead of `= ANY`, it's much easier to understand for most people. — dnoeth, Nov 09 '18 at 11:42

score 0 · Answer 1 · answered Nov 09 '18 at 11:46

You need to define the Delimiter to something else (eg: $$) other than ;. At the end redefine back to ;
Please do not use comma based Implicit joins. These are obsolete and error-prone. Please switch to modern JOIN .. ON based syntax.
Switch to IN instead of = ANY:

Try the following instead:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_cand_by_pos_skill`(IN id INT)
BEGIN

SELECT *  
FROM candidates 
WHERE cid IN (SELECT candid 
              FROM positionskills p 
              JOIN candidate_skills s ON s.skillid = p.skill
              WHERE p.positionid = id);

END $$
DELIMITER ;

What's wrong with this stored procedure (nested SELECT)?

1 Answers1