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