In MySQL how do I create a stored procedure that takes multiple parameters?

Question

Example of function: call getThings(amount, place, limit, marginError)

SYNOPSIS: CALL getThings(4, PA, 3, 1.2);

example of goal:

CREATE PROCEDURE getThings(IN amount X, place VARCHAR(30), lim INT, marginError double)
SELECT place, limit, amount
FROM AREA, PRODUCT
WHERE AREA.place=PRODUCT.place
AND PRODUCT.AREA=place
ORDER BY ABS(AMOUNT-marginError)
LIMIT lim;
END

Desired goal is to retrieve the closest 3 products from a stored procedure (using MySQL) but I keep getting sytax errors in trying to create the procedure.

Solution 1

since you didn't post the exact error/message,

EDIT: I assume you are missing the IN/OUT for the 2.and 3. parameter. - Not true, see comments.

e.g.

DELIMITER$$
CREATE PROCEDURE getThings(IN amount X, IN place VARCHAR(30), IN lim INT)
   SELECT place, `limit`, amount
   FROM AREA, PRODUCT
   WHERE AREA.place=PRODUCT.place
   AND PRODUCT.AREA=place
   ORDER BY ABS(AMOUNT-5)
   LIMIT lim;
END$$
DELIMITER;

Solution 2

LIMIT is MySQL's reserved word. If you really need to use it as column name, put in in backticks (``). Also, your paramteres have same names as columns in your table, which adds to confusion.

Comments

[stackoverflow]

sorry that was a typo when cranking out the question (its been corrected)

[stackoverflow]

No, it never was. I had lim in there

[Mchl]

SELECT place, limit, amount <-- you sure there's no limit in here?

[Bernhard Kircher]

I am affraid you had LIMIT in your first version of your question, since i copied your code for the first version of my answer...

[Mchl]

IN/OUT/INOUT specifiers are optional. By default parameters are IN