Set default value for proceduce in mysql

Question

I want write proceduce have parameter which have default value if no value pass to it, something like this:

CREATE DEFINER = 'root'@'localhost'
PROCEDURE client_logging_system.Proc_client_Get(IN in_clientID INT DEFAULT 1)
  COMMENT '
-- Proc for get user
-- Parameter:
-- in_clientID: ID of client
'
BEGIN
  SELECT c.ID, c.Name, c.BrokerID, c.LoginID, c.Password, c.`GroupID`, c.Quanity, c.ApiKey, c.ToTp FROM `client` c
  WHERE c.ID = in_clientID;
END

Any one have suggestion about this problem?

This has been an open feature request of MySQL stored procedures since 2005: https://bugs.mysql.com/bug.php?id=15975. I don't think you should expect it to be implemented. — Bill Karwin, Dec 27 '22 at 07:01

score 0 · Answer 1 · answered Dec 27 '22 at 06:40

If the parameter is defined then you MUST provide a value for it. You cannot skip the value.

If you do not want to specify the value and apply some "default" onу then you'd do this in the code:

CREATE DEFINER = 'root'@'localhost'
PROCEDURE client_logging_system.Proc_client_Get(IN in_clientID INT)
BEGIN
SET in_clientID = COALESCE(in_clientID, 1);  -- if NULL is provided then set to default value 1
...

Accordingly you use CALL client_logging_system.Proc_client_Get(123) for definite parameter value and CALL client_logging_system.Proc_client_Get(NULL) for to apply default value.

Set default value for proceduce in mysql

1 Answers1