1

Define procedure:

DELIMITER $$
CREATE PROCEDURE SP_Reporting(IN tablename VARCHAR(20))
proc_label:BEGIN
     IF tablename IS NULL THEN
            SELECT 'Null detect';
          LEAVE proc_label;
     END IF;

     SELECT 'after';
END;
$$
DELIMITER ;

Call Procedure:

CALL SP_Reporting();

Error :

ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE cds.SP_Reporting ; expected 1, got 0

How pass var by default like SP_Reporting(IN tablename = 'default value' VARCHAR(20))

sqluser
  • 5,502
  • 7
  • 36
  • 50
Michael Phelps
  • 3,451
  • 7
  • 36
  • 64
  • Pehaps CALL SP_Reporting(null); ? – jarlh Jun 18 '15 at 06:58
  • 1
    duplicate: [Writing optional parameters within stored procedures in MySQL?](https://stackoverflow.com/questions/12652241/writing-optional-parameters-within-stored-procedures-in-mysql). Also: [Is it possible to have a default parameter for a mysql stored procedure?](https://stackoverflow.com/questions/982798/is-it-possible-to-have-a-default-parameter-for-a-mysql-stored-procedure) – Ryan Vincent Jun 18 '15 at 07:07

3 Answers3

3

When you are making your stored procedure, you can assign a value to your input, so there is no need to pass parameters while you are calling the proc.

We usually assign NULL and for making parameters optional, we use this method.

tablename VARCHAR(20) = NULL

Your complete script:

DELIMITER $$
CREATE PROCEDURE SP_Reporting(IN tablename VARCHAR(20) = NULL)
proc_label:BEGIN
     IF tablename IS NULL THEN
            SELECT 'Null detect';
          LEAVE proc_label;
     END IF;

     SELECT 'after';
END;
$$
DELIMITER ;

EDIT

MySQL is not accepting optional parameters. So one way is to pass NULL value in your stored procedure and check it with IF statement inside your proc.

DELIMITER $$
CREATE PROCEDURE SP_Reporting(IN tablename VARCHAR(20))
proc_label:BEGIN
     IF tablename IS NULL THEN
            -- Do something;
     ELSE
            -- Do something else;
     END IF;
END;
$$
DELIMITER ;
sqluser
  • 5,502
  • 7
  • 36
  • 50
2

You have to pass the table name in the procedure call statement like:

CALL SP_Reporting(table_name);

you can't pass default in call statement. You can assign default value before calling the procedure. or use OUT instead of IN as a parameter.

Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81
0

you miss the parameter :tablename you should like this : call SP_Reporting('any varchar')
or call SP_Reporting(null)

cary72
  • 1