How to call a stored procedure using select statement in mysql

Question

I have call statement like

 CALL report_procedure
('2013-02-01',now(),'2015-01-01','1');

and i want to use it in a select query. i have tried like

Select * from ( CALL report_procedure
    ('2013-02-01',now(),'2015-01-01','1'));

but error occurs. like

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ( CALL report_procedure ('2013-02-01',now(),'2015-01-01','1') at line 3 0.297 sec

Can anyone suggest me a method to call stored procedure in Select statement in mysql??

This is because MySQL doesn't allow procedure call inside select statements. Please share your objective which you want to achieve with the procedure. — Abhishek
– Abhishek, Commented Aug 20, 2015 at 12:32
Actually i need to use conditions for result of the call statement i.e. result of the stored procedure. if i can store the result in a variable or use the call statement in select query i will be able to use conditions for the stored procedure — Vineeth Prabhakaran
– Vineeth Prabhakaran, Commented Aug 20, 2015 at 12:40

Devart · Accepted Answer · 2015-08-20 12:48:16Z

22

It is not possible to use result set from procedure in FROM clause. MySQL does not allow doing this.

You may populate another table (or temporary table) in your procedure, and after, use that table in SELECT commands -

CALL report_procedure ('2013-02-01',now(),'2015-01-01','1'); -- fill temp_table
SELECT * FROM temp_table;

answered Aug 20, 2015 at 12:48

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ladieu Over a year ago

you can just use a function instead of a sproc if you want to call it from a select

Shahrukh Over a year ago

@ladieu MYSQL doesn't support table-valued function we must use the temp_table with stored procedure.

latsi · Accepted Answer · 2015-08-20 12:45:47Z

-4

--Firstly your store procedure should look something like this:

CREATE PROCEDURE report_procedure(
IN d1 DATE,
dnow DATE,
d2 DATE,
val INT
) 
BEGIN SELECT * 
FROM yourtablename
WHERE date1 = d1
AND datenow > dnow
AND date2 > d2
AND value = val;

END
--The store procedure contains the select statement.

-- then you can call the store procedure like that:
 CALL report_procedure('2013-02-01',now(),'2015-01-01','1');

--hope it helps

answered Aug 20, 2015 at 12:45

latsi

192 bronze badges

1 Comment

Duc Toan Pham Over a year ago

What this post wanted to SELECT from resultset of the CALL

Collectives™ on Stack Overflow

How to call a stored procedure using select statement in mysql

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related