CODEWITHSUNDEEP
mysqlselectstored-procedures
Vineeth Prabhakaran
Vineeth Prabhakaran

Reputation: 641

How to call a stored procedure using select statement in mysql

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??

Upvotes: 16

Views: 58596

Answers (2)

Devart
Devart

Reputation: 122042

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;

Upvotes: 22

latsi
latsi

Reputation: 19

--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

Upvotes: -4

Related Questions