Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10862

Stored procedures: default values for parameters (optional parameters)

Details

    Description

      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

      Attachments

        Issue Links

          Activity

            So an important feature is not implemented.

            laaneoks Erkki Laaneoks added a comment - So an important feature is not implemented.

            I think this issue is stalled because it's unassigned. I wait for it too long. Can someone argue why this feature is still unimplemented?

            balta Tadas Balaišis added a comment - I think this issue is stalled because it's unassigned. I wait for it too long. Can someone argue why this feature is still unimplemented?
            karll Karl Levik added a comment -

            I think this issue is stalled because it's unassigned. I wait for it too long. Can someone argue why this feature is still unimplemented?

            Hi @Tadas Balaišis - I think it simply needs a bit more votes so that whoever decides the roadmap can see it's a popular feature request.

            karll Karl Levik added a comment - I think this issue is stalled because it's unassigned. I wait for it too long. Can someone argue why this feature is still unimplemented? Hi @Tadas Balaišis - I think it simply needs a bit more votes so that whoever decides the roadmap can see it's a popular feature request.

            Use bb-11.8-bar-MDEV-10862-opt-params for testing. Also, see the PR

            serg Sergei Golubchik added a comment - Use bb-11.8-bar- MDEV-10862 -opt-params for testing. Also, see the PR
            susil.behera Susil Behera added a comment -

            I've completed my testing with the following test cases.
            1. Default data types including JSON and VECTOR.
            2. Expressions as default value
            3. Other stored routines in default value expression
            4. Out of range value
            5. Invalid data type in default value
            6. NULL value
            7. IN, OUT, INOUT parameters in a stored procedure
            8. Packages
            9. User defined functions
            10. Parameter positions with both allowed and not-allowed default values.

            The test results are looking good. OK for push from QA.

            susil.behera Susil Behera added a comment - I've completed my testing with the following test cases. 1. Default data types including JSON and VECTOR. 2. Expressions as default value 3. Other stored routines in default value expression 4. Out of range value 5. Invalid data type in default value 6. NULL value 7. IN, OUT, INOUT parameters in a stored procedure 8. Packages 9. User defined functions 10. Parameter positions with both allowed and not-allowed default values. The test results are looking good. OK for push from QA.

            Thanks Susil!

            bar Alexander Barkov added a comment - Thanks Susil!

            Great news. I wonder does it include setting parameter by name? I mean, if

            PROCEDURE sp((IN p1 INT DEFAULT 1, IN p2 INT default 0)

            how should I set only second paremeter?

            sp(default,10);
            sp(\N,10);

            or (by name)

            sp(p2 => 10)
            ?

            It would by great not to set some parameters including those having earlier position.

            balta Tadas Balaišis added a comment - Great news. I wonder does it include setting parameter by name? I mean, if PROCEDURE sp((IN p1 INT DEFAULT 1, IN p2 INT default 0) how should I set only second paremeter? sp(default,10); sp(\N,10); or (by name) sp(p2 => 10) ? It would by great not to set some parameters including those having earlier position.

            Passing by name is not included into this task.

            It will be done separately, under terms of this ticket:
            MDEV-35965 Oracle: Named and Mixed notations for stored routine call parameters

            bar Alexander Barkov added a comment - Passing by name is not included into this task. It will be done separately, under terms of this ticket: MDEV-35965 Oracle: Named and Mixed notations for stored routine call parameters

            People

              bar Alexander Barkov
              karll Karl Levik
              Votes:
              27 Vote for this issue
              Watchers:
              26 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.