1

I have a routine that is called using 

SET @p0='55'; SET @p1='-6'; SET @p2='100'; CALL `distanceSearch`(@p0, @p1, @p2);

Basically i pass in latitude, longitude and a distance to search for users, e.g 100 miles. The routine creates a temp table and inserts results into it. When i execute it, a result set is returned.

When i try execute it like this i run into a syntax error

SET @p0='55'; SET @p1='-6'; SET @p2='100'; 
select foo.* from (CALL `distanceSearch`(@p0, @p1, @p2)) as foo

What am i doing wrong? How do use the results from this to join on another table?

user2202098
  • 830
  • 1
  • 9
  • 24

1 Answers1

1

NO, you can't perform a select from procedure like that way but as you said The routine creates a temp table and inserts results into it

in that case if you already know the temporary table name then do a SELECT from that table 

select * from temporary_table_name

Else, convert your routine to a table valued function rather than a stored procedure and then you can say

select * from fn_runRoutine_Job()
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • 1
    `select * from fn_runRoutine_Job()`... stored functions only return scalars in MySQL. There are no "table valued functions." – Michael - sqlbot Dec 27 '15 at 21:43
  • 1
    @Michael-sqlbot, Yes exactly, might be a miss from my side. Only way is to go with a temporary table. – Rahul Dec 28 '15 at 15:20
  • Thanks, i realised only one column is needed e.g the calculated distance , so a function would suffice here. – user2202098 Dec 28 '15 at 20:57