Is it possible to have a default parameter for a mysql stored procedure?

I have googled this and keep coming up with "No it is not possible" but these posts were dated 2005-2007 so I'm wondering if this has been changed. A code example:

CREATE PROCEDURE `blah`
(
myDefaultParam int = 0 -- This breaks the code for some reason
)
BEGIN
-- Do something here
END

One of the solutions has been to pass null and then check for null and set the variable. I don't want to do that and I shouldn't have to. If this is true then MySql devs need to wake up because there is so much more I could do with MSSQL.

126256 次浏览
小开
最佳答案

It's still not possible.

小开

If you look into CREATE PROCEDURE Syntax for latest MySQL version you'll see that procedure parameter can only contain IN/OUT/INOUT specifier, parameter name and type.

So, default values are still unavailable in latest MySQL version.

小开

No, this is not supported in MySQL stored routine syntax.

Feel free to submit a feature request at bugs.mysql.com.

小开

We worked around this limitation by adding a simple IF statement in the stored procedure. Practically we pass an empty string whenever we want to save the default value in the DB.

CREATE DEFINER=`test`@`%` PROCEDURE `myProc`(IN myVarParam VARCHAR(40))
BEGIN
IF myVarParam = '' THEN SET myVarParam = 'default-value'; END IF;


...your code here...
END
小开

Unfortunately, MySQL doesn't support DEFAULT parameter values, so:

CREATE PROCEDURE `blah`
(
myDefaultParam int DEFAULT 0
)
BEGIN
-- Do something here
END

returns the error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'DEFAULT 0) BEGIN END' at line 3

To work around this limitation, simply create additional procedures that assign default values to the original procedure:

DELIMITER //


DROP PROCEDURE IF EXISTS blah//
DROP PROCEDURE IF EXISTS blah2//
DROP PROCEDURE IF EXISTS blah1//
DROP PROCEDURE IF EXISTS blah0//


CREATE PROCEDURE blah(param1 INT UNSIGNED, param2 INT UNSIGNED)
BEGIN
SELECT param1, param2;
END;
//


CREATE PROCEDURE blah2(param1 INT UNSIGNED, param2 INT UNSIGNED)
BEGIN
CALL blah(param1, param2);
END;
//


CREATE PROCEDURE blah1(param1 INT UNSIGNED)
BEGIN
CALL blah2(param1, 3);
END;
//


CREATE PROCEDURE blah0()
BEGIN
CALL blah1(4);
END;
//

Then, running this:

CALL blah(1, 1);
CALL blah2(2, 2);
CALL blah1(3);
CALL blah0();

will return:

+--------+--------+
| param1 | param2 |
+--------+--------+
|      1 |      1 |
+--------+--------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


+--------+--------+
| param1 | param2 |
+--------+--------+
|      2 |      2 |
+--------+--------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


+--------+--------+
| param1 | param2 |
+--------+--------+
|      3 |      3 |
+--------+--------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)


+--------+--------+
| param1 | param2 |
+--------+--------+
|      4 |      3 |
+--------+--------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)

Then, if you make sure to only use the blah2(), blah1() and blah0() procedures, your code will not need to be immediately updated, when you add a third parameter to the blah() procedure.

小开 
SET myParam = IFNULL(myParam, 0);

Explanation: IFNULL(expression_1, expression_2)

The IFNULL function returns expression_1 if expression_1 is not NULL; otherwise it returns expression_2. The IFNULL function returns a string or a numeric based on the context where it is used.


提交答案