Stored procedures: default values for parameters (optional parameters)

It would be nice if stored procedures supported parameters with default values. This way, if I need to modify a stored procedure to add more parameters to it, my old code with calls to the SP can continue to work. Also, this missing feature could be a blocker for migrations from other SQL DBMSes to MariaDB.

To clarify, I would like to be able to do something like:

DELIMITER //

CREATE PROCEDURE sp(IN p1 INT DEFAULT 1) BEGIN SELECT p1; END //

DELIMITER ;

CALL sp();

+------+

| p1   |

+------+

|    1 |

+------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

This feature exists e.g. in:
* MS-SQL Server
* PostgresSQL: for functions and for procedures
* SAP HANA. SAP HANA also supports named parameters
* Oracle 10g - note that the value can be an expression
* Sybase - note that the value can be an expression
* DB2
* MemSQL

Note that this is also a long-standing request for MySQL, and users continue to comment on the request to say how useful it would be, e.g. when converting from other DBMSes.

[25 Dec 2005 6:22] Valerii Kravchuk

Thank you for a useful feature request. By optional parameter you mean the one with default value, I believe... So, that the value for it can be ommited in the SP call. Oracle's PL/SQL also has this feature. I think it should be implemented as soon as possible to simplify migration from other RDBMS to MySQL 5.

https://bugs.mysql.com/bug.php?id=15975