CODEWITHSUNDEEP
mysqldatabasestored-procedurescall
stackoverflow
stackoverflow

Reputation: 19474

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

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.

Upvotes: 8

Views: 41394

Answers (2)

Bernhard Kircher
Bernhard Kircher

Reputation: 4182

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;

Upvotes: 13

Mchl
Mchl

Reputation: 62395

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.

Upvotes: 7

Related Questions