MySQL Stored Procedure with Parameters for Recursive CTE-CodePudding

I'm working on a MySQL Way of printing an "affiliate tree" and got the thing working with Common Table Expression. I'm using the following code right now:

WITH RECURSIVE recUsers AS
(
  SELECT ID, username, sponsorID, 1 AS depth, username AS path
    FROM users
    WHERE id = 1
  UNION ALL
  SELECT c.ID, c.username, c.sponsorID, sc.depth   1, CONCAT(sc.path, ' > ', c.username)
    FROM recUsers AS sc 
      JOIN users AS c ON sc.ID = c.sponsorID
)
SELECT * FROM recUsers;

This selects the tree underneath the user with the id 1.

Now what I'd need to get is a way to pass that id as a parameter, so I don't need to define everything from the beginning every time I want to get the result.. So my idea is to put everything in a stored prodecure and pass the id in as a parameter.. However, so far I didn't get it working and always getting various errors that are very self speaking...

Basically what I've tried was

DELIMITER //
CREATE PROCEDURE getAffiliateTree(IN userid INT())
BEGIN
---my code here, the userid 1 replaced with userid
END//
DELIMITER;

However, this doesn't seem to work.. How can I get this done?

CodePudding user response：

Two things I would suggest:

Use INT, not INT(). The optional length argument to integer types is deprecated in MySQL 8.0 (which I know you're using, because you're using CTE syntax). Even if you did use the length argument, using an empty argument is not legal syntax.

Make sure that the userid input parameter name is distinct from all of the columns in the tables you reference. That is, if the table has a column named userid (any capitalization), then change the name of your input parameter. Otherwise you may make ambiguous expressions like:

... WHERE userid = userid

Even though you intend one of these to be the column and the other to be the parameter, the SQL parser has no way of knowing that. It ends up treating both as the column name, so it's trivially true on all rows of the table.

Actually, a third thing I would suggest: when you ask questions, "it doesn't seem to work" isn't clear enough. Did it produce an error? If so, what was the full error message? Did it produce no error, but didn't give you the result you wanted? If so, show a mocked-up example of what you expected, and what the query produced that didn't match. It helps to be as clear as you can when you post questions, so readers don't have to guess what trouble you need help with.