How to call a stored procedure using select statement in MySQL?

In MySQL, it is not possible to use select from procedure in FROM clause. You can use CALL command and after that the SELECT statement can be executed.

Let us first create a table:

mysql> create table DemoTable2
   -> (
   -> CustomerName varchar(100),
   -> ShippingDateTime datetime
   -> );
Query OK, 0 rows affected (0.66 sec)

Following is the query to create stored procedure:

mysql> DELIMITER //
mysql> CREATE PROCEDURE insert_information(Name varchar(100),shippingtime datetime)
   -> BEGIN
   -> INSERT INTO DemoTable2(CustomerName,ShippingDateTime) VALUES(Name,shippingtime);
   -> END
   -> //
Query OK, 0 rows affected (0.16 sec)

mysql> DELIMITER ;

Now you can call the stored procedure using call command:

mysql> call insert_information('Chris',NOW());
Query OK, 1 row affected, 1 warning (0.15 sec)

Here is the query to display records from the table using select statement after calling stored procedure.

mysql> select *from DemoTable2;

This will produce the following output

| CustomerId | CustomerName | ShippingDateTime    |
| 1          | Chris        | 2019-04-08 15:03:07 |
1 row in set (0.00 sec)

Updated on: 30-Jul-2019

5K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started