Optional Parameters in Mysql stored procedures

Question

How do I create an optional parameter in a mysql stored procedure?

Answer 1

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.

Answer 2

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);

Answer 3

According to this bug, there is currently no way to create optional parameters.