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

• Related Questions & Answers
• How to call a stored procedure using callable statement in JDBC explain?
• How can a MySQL stored procedure call another MySQL stored procedure inside it?
• How to set two variables in a stored procedure with a single MySQL select statement?
• How to correctly implement END IF statement in a MySQL Stored Procedure?
• How MySQL IF statement can be used in a stored procedure?
• How Can MySQL LOOP statement be used in a stored procedure?
• How Can MySQL CASE statement be used in stored procedure?
• How to call an existing stored procedure in a database using JDBC API?
• How to use IF in stored procedure and select in MySQL?
• Call Stored Procedures within a Stored Procedure with IF Logic?
• MySQL procedure to display a “select” statement twice
• How MySQL IF ELSE statement can be used in a stored procedure?
• How to call a stored procedure that accepts input parameters, using JDBC program?
• How to call a stored procedure that returns output parameters, using JDBC program?
• How MySQL WHILE loop statement can be used in stored procedure?