how to call a mysql stored procedure with arguments from command line

Question

How can I call a stored procedure from command line?

I have a procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `insertEvent`(IN `dateTimeIN` DATETIME)
    NO SQL
BEGIN
    SET @eventIDOut = NULL;

    IF  EXISTS(SELECT * FROM `events` WHERE `eventDate` = dateTimeIN) THEN
        SELECT `eID` INTO @eventIDOut FROM `events` WHERE `eventDate` = dateTimeIN LIMIT 1;
        ELSE
        INSERT INTO `events` (`eventDate`) VALUES(dateTimeIN);
        SET @eventIDOut = last_insert_id();
    END IF;

    SELECT CONCAT(@eventIDOut);
END

Answer 1

Stored procedure: A stored procedure is an SQL Query that you can save and run whenever you need the query in the future. It helps to increase the reusability of the code as we don’t have to write the entire query again and save time by just calling that query by a  name.

 

Let’s have a look mon code how to call  it :

 

CREATE DEFINER=`admin`@`localhost` PROCEDURE `invoking`(IN `dateTimeIN` DATETIME)

    NO SQL

BEGIN

    SET @scheduleIDOut = NULL;

 

    IF  EXISTS(SELECT * FROM `schedule` WHERE ‘scheduleDate` = dateTimeIN) THEN

        SELECT `sID` INTO @scheduleIDOut FROM `schedule` WHERE `scheduleDate` = dateTimeIN LIMIT 1;

        ELSE

        INSERT INTO `schedule` (`scheduleDate`) VALUES(dateTimeIN);

        SET @scheduleIDOut = last_insert_id();

    END IF;

 

    SELECT CONCAT(@scheduleIDOut);

END

 

Calling procedure eith argument from  command line:

 

mysql> CALL invoking ('2021.05.02 12:12:12');