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
   -> (
   -> CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> 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)

Rama Giri

Published on 17-Apr-2019 11:36:10

Previous Page Print Page