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.

related : http://stackoverflow.com/questions/91150/mysql-stored-procedure — Haim Evgi, Feb 03 '11 at 11:45
duplicate: http://stackoverflow.com/questions/12652241/writing-optional-parameters-within-stored-procedures-in-mysql — Patrick Allaert, Feb 06 '14 at 09:41

score 5 · Answer 1 · edited May 23 '17 at 10:30

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

I don't see where's the optional parameter in your UDF. Besides, `SQL ERROR(1064) You have an error in your SQL Syntax...` — Pacerier, Apr 16 '15 at 07:49
I don't think this statement is correct: "You can however set optional parameters in a MySQL UDF". You should probably say something like this: "You cannot set an optional parameter in a MySQL UDF, but here's a work-around". You can't have an optional parameter in a Stored Proc, and I'm pretty sure the same is true of a UDF. — Tom, Aug 06 '15 at 13:26
@Tom a UDF is something different from a mysql user function. It is a function that you write in C/C++, which can be imported into MySQL, and it does support optional arguments. — juacala, Jul 15 '17 at 01:21

score 4 · Answer 2 · answered Oct 05 '12 at 14:11

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.

score 1 · Answer 3 · edited Mar 23 '16 at 22:03

Another approach is to pass only one 'super' parameter which is string with commas in it separating the real parameters. The mysql procedure can then parse the 'super' parameter into the separate real parameters. Example:

create procedure procWithOneSuperParam(param1 varchar(500))
declare param2 varchar(100);
begin
 if LOCATE(',',param1) > 0 then
  .. param2=<extract the string after the ',' from param1> ..

Create a function with optional arguments in MySQL

3 Answers3

Linked

Related