Bug #60355 Stored Procedures doesnt accept variable number of arguments and default values
Submitted: 5 Mar 2011 18:08 Modified: 5 Mar 2011 18:32
Reporter: Claudio Nanni Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version:5.1+ OS:Any
Assigned to:
Tags: Parameters, stored procedures

[5 Mar 2011 18:08] Claudio Nanni
Description:
I think it would really be useful to have the chance to have functions that accept a variable number of parameters and the possibility to specify a default value for each not specified parameter. I think SP in mysql are not enough used also because their language is not as powerful as Oracle's and Microsoft's.

From the manual I dont see anyway to do it: http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html

How to repeat:
DELIMITER |
   CREATE PROCEDURE myproc(p1 int,p2 int, p3 int)
   BEGIN
      select p1,p2,p3;
   END |
Query OK, 0 rows affected (0.00 sec)
DELIMITER ;

mysql> call myproc(1);
Connection id:    81042
Current database: test

ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE test.myproc; expected 3, got 1

Suggested fix:
Allow variable number of arguments and default values.
It can allow a much more concise code.
The alternative is to use a different function for each argument set, that sounds ridiculous.
My current home made solution is based on passing all the arguments within quotes and as a single defined arguments with a custom separator but its quite a overhead since you have to split arguments manually.
[5 Mar 2011 18:32] Valerii Kravchuk
This is a duplicate of Bug #15975. See Bug #15813 also.