Create a function with optional arguments in MySQL

Question

I want to create a function with optional arguments in MySQL. For instance, I want to create function that calculates the average of its arguments. I create a function of five arguments, but when user passes just two arguments to the function then it should still run and return the average of the two arguments.

Answer 1

You cannot set optional parameters in MySQL stored procedures.
You can however set optional parameters in a MySQL UDF.

You do know that MySQL has an AVG aggregate function?

Workaround If you can face the ugliness of this workaround here's samplecode that uses a comma separated string with values as input and returns the average.

DELIMITER $$  CREATE FUNCTION MyAvg(valuestr varchar) RETURNS float BEGIN   DECLARE output float;   DECLARE arg_count integer;   DECLARE str_length integer;   DECLARE arg float;   DECLARE i integer;    SET output = NULL;    SET i = LENGTH(valuestr);   IF i > 0 THEN BEGIN       SET arg_count = 1;     WHILE i > 0 DO BEGIN       IF MID(valuestr, i, 1)       SET i = i - 1;     END; END WHILE;      /* calculate average */     SET output = 0;     SET i = arg_count;     WHILE i > 0 DO BEGIN       SET arg = SUBSTRING_INDEX(                    SUBSTRING_INDEX(valuestr, ',' , i)                   , ',', -1 );       SET output = output + arg;       SET i = i - 1;      END; END WHILE;            SET output = output / arg_count;    END; END IF;       RETURN output; END $$  DELIMITER ; 

Use concat_ws to feed the function.

SELECT MyAvg(CONCAT_WS(',',100,200,300,500)) AS test; 

You can also write an UDF in C(++) or Delphi/Lazarus

Answer 2

While far from an ideal solution, here's how I solved optional parameters for a concat function I needed:

delimiter || create function safeConcat2(arg1 longtext, arg2 varchar(1023))  returns longtext  return safeConcat3(arg1, arg2, ''); ||  create function safeConcat3(arg1 longtext, arg2 varchar(1023), arg3 varchar(1023))  returns longtext  return safeConcat4(arg1, arg2, arg3, ''); ||  create function safeConcat4(arg1 longtext, arg2 varchar(1023), arg3 varchar(1023), arg4 varchar(1023)) returns longtext   begin       declare result longText;       set result = concat(arg1, arg2, arg3, arg4);       if( result is null) then           set result=arg1;       end if;       return result;   end || 

Note: This means you have to call the method that corresponds to the number of args.