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

User · Accepted Answer

It s still not possible

User · Answer

No  this is not supported in MySQL stored routine syntax   Feel free to submit a feature request at bugs mysql com

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to mysql

Examples related to stored-procedures

Examples related to default-value