Named parameters for stored procedures in MySQL

Question

Can I use named parameters for stored procedures in MySQL? I need to call stored procedures something like this:

CALL stored_procedure(@param1 = "foo1", @param2 = "foo2");

The answer is

CALL stored_procedure(@param1 := "foo1", @param2 := "foo2");

Thanks for Pentium10.

Answer 1

MySQL stored procedures do not support named parameters in any order. The feature request has been made in the past, for example:

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

As you discovered, the workaround to use expressions to set session variables has nothing to do with the order of parameters. In other words, you can do the following:

CALL stored_procedure(@param1 := "foo1", @param2 := "foo2");

But the values "foo1" and "foo2" are still the first and second parameters, respectively.

If you want more flexibility, I suggest you write your stored procedure to accept no parameters, but use session variables directly.

SET @param1 := "foo1", @param2 := "foo2";
CALL stored_procedure();

In your procedure, you must then reference the variables as @param1, @param2, etc.

CREATE PROCEDURE stored_procedure()
BEGIN
    SELECT * FROM mytable WHERE col1 = @param1 AND col2 = @param2;
END

Read http://dev.mysql.com/doc/refman/5.7/en/user-variables.html

Answer 2

This is not possible formally.

But there is a workaround that can help. Use this:

CALL prcInsertStuff ( 
@paramName1 := nameValue1 
, @paramValue1 := paramValue1 
);