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

Question

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.

duplicate: http://stackoverflow.com/questions/12652241/writing-optional-parameters-within-stored-procedures-in-mysql — Patrick Allaert, Feb 06 '14 at 09:41

score 91 · Accepted Answer · answered Jun 11 '09 at 18:29

91

It's still not possible.

answered Jun 11 '09 at 18:29

Paul Sonier

38,903
3
77
117

9

Is there any workaround? Like checking if the parameter is null then give it a default value? – papaiatis Mar 13 '12 at 14:33
1

@papaiatis Yes you can just add an if statement, see my other post below. – Dive50 Jul 09 '12 at 23:36
3

I don't know why this is the accepted answer when below here @Dive50 has a useful workaround, which I'm about to implement because I'm facing the same problem. – bokov Nov 02 '12 at 13:58
4

That's why I hate MySQL. Such a basic thing still not there – Kamran Shahid Oct 28 '14 at 10:44
2

It's not possible to use the syntax suggested but passing NULL and then using IFNULL is perfectly possible and achieves exactly the same thing. – Hans Jul 08 '16 at 15:46
does mariaDB has this same problem? – Webber Depor Sep 03 '17 at 18:32

score 56 · Answer 2 · answered Jul 09 '12 at 23:34

56

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

answered Jul 09 '12 at 23:34

Dive50

819
6
8

12

Why not use `null` instead? – Pacerier Apr 16 '15 at 07:45
2

this shows how much love you can give to mysql, meanwhile in sql you can simply put " param_name int(11) = NULL" ... thank you Oracle – Sebastien H. Jul 04 '16 at 22:15

score 37 · Answer 3 · edited Mar 31 '16 at 14:21

37

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.

edited Mar 31 '16 at 14:21

Daniel Böhmer

14,463
5
36
46

answered Mar 04 '15 at 14:36

SKManX

3,389
2
16
12

2

this is more efficient way to do it. – KD.S.T. May 09 '18 at 00:38

score 12 · Answer 4 · answered Jun 11 '09 at 18:34

12

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.

answered Jun 11 '09 at 18:34

Michael

903
1
8
16

score 6 · Answer 5 · answered Oct 02 '12 at 16:02

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.

score 1 · Answer 6 · answered Jun 11 '09 at 18:40

1

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

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

answered Jun 11 '09 at 18:40

Bill Karwin

538,548
86
673
828

2

Posting this from the other question I asked: http://bugs.mysql.com/bug.php?id=15975 – aarona Jun 12 '09 at 06:41

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

6 Answers6

Linked

Related