1

I need to create a MYSQL procedure where the procedure accepts several parameters and works with them. However, in the case where it is not present, the parameter variables pick some 'default' values and continue. Similar to how the pseudo-function-overload is handled in PHP.

This code is what I could come up with.

CREATE PROCEDURE PROC_INS_CONTENT_TEST(IN DATA_VAL LONGTEXT)
BEGIN
    IF (DATA_VAL IS NULL) THEN SET DATA_VAL='DEFAULT'; END IF;
    INSERT INTO CONTENT_TEST (DATA) VALUES (DATA_VAL);
END

And this code does not work the way I want it to behave. Is there a way to assign the default value to the variable right when the parameter is declared?

Samik Sengupta
  • 1,944
  • 9
  • 31
  • 51
  • http://stackoverflow.com/questions/982798/is-it-possible-to-have-a-default-parameter-for-a-mysql-stored-procedure – ethrbunny Nov 23 '12 at 13:28

1 Answers1

2

Yes there is.

If you want to set default value on variable on your function/procedure you can do this:

CREATE PROCEDURE PROC_INS_CONTENT_TEST(IN DATA_VAL LONGTEXT)
BEGIN
    DECLARE my_data_double DOUBLE DEFAULT 0;
    DECLARE my_data_varchar VARCHAR(20) DEFAULT 'default_value';
    DECLARE DATA_VAL LONGTEXT DEFAULT 'default_value';
    DECLARE my_data_integer INT DEFAULT 0;
END

Or if you want to assign value you can do something like:

SET my_data_value = 12345;

inside your function/procedure.

SubRed
  • 3,169
  • 1
  • 18
  • 17
  • It seems MySQL really doesn't support what I require. But this is closer to what I required. But it still doesn't prevent an error when the Procedure is called without the required arguments. Thank you. – Samik Sengupta Nov 24 '12 at 05:42