Writing optional parameters within stored procedures in MySQL?

Question

I would like to create a stored procedure which updates either all fields in a table or just a few of them according to parameters passed to it.

How do I create a stored procedure that accepts optional parameters?

Answer 1

Optional Parameters are not yet supported on MySQL. I'm suggesting that you pass null value in your parameter and inside your stored procedure has an IF statement.

DELIMITER $$
CREATE PROCEDURE procName
(IN param VARCHAR(25))
BEGIN
   IF param IS NULL THEN 
      -- statements ;
   ELSE commands
      -- statements ;
   END IF;
END$$
DELIMITER ;

Answer 2

An special case is when the parameter cant' be NULL, i.e. because is a key. I use a trick for these case: I set the parameter to -1:

CREATE PROCEDURE procCreate
(IN id_cosa INT(11))
  BEGIN
    IF id_cosa != -1 THEN
      ~~(your code here)~~
    END IF
  END