How do I create an optional parameter in a mysql stored procedure?
Asked
Active
Viewed 2.4k times
8
-
duplicate: http://stackoverflow.com/questions/12652241/writing-optional-parameters-within-stored-procedures-in-mysql – Patrick Allaert Feb 06 '14 at 09:35
3 Answers
5
Optional parameters are not supported in mySQL stored procedures, nor are there any current plans to add this functionality at this time. I'd recommend passing null for optional parameters.
Cory House
- 14,235
- 13
- 70
- 87
4
The (somewhat clunky) workaround I used was to define a number of parameters that I thought I might use and then test to see if the optional parameter's value is NOT NULL before using it:
CREATE PROCEDURE add_product(product_name VARCHAR(100), product_price FLOAT,
cat1 INT, cat2 INT, cat3 INT)
-- The cat? parameters are optional; provide a NULL value if not required
BEGIN
...
-- Add product to relevant categories
IF cat1 IS NOT NULL THEN
INSERT INTO products_to_categories (products_id, categories_id) VALUES (product_id, cat1);
END IF;
IF cat2 IS NOT NULL THEN
INSERT INTO products_to_categories (products_id, categories_id) VALUES (product_id, cat2);
END IF;
IF cat3 IS NOT NULL THEN
INSERT INTO products_to_categories (products_id, categories_id) VALUES (product_id, cat3);
END IF;
END
If I don't want to use the parameter when calling the stored, I simply pass a NULL value. Here's an example of the above stored procedure being called:
CALL add_product("New product title", 25, 66, 68, NULL);
Anthony Geoghegan
- 11,533
- 5
- 49
- 56
-
The IFNULL() function fits very nicely into this context; I just implemented it inline, in the VALUES list of an INSERT query. – David A. Gray Mar 05 '18 at 22:12
