Is it possible to call a procedure within an SQL statement?

Question

Hi everyone

I thought I would use a stored routine to clean up some of my more complex SQL statements. From what I've read, it seems impossible to use a stored procedure within an sql statement, and a stored function only returns a single value when what I need is a result set. I am using mySQL v5.0

SELECT p.`id`, gi.`id`
FROM `sport`.`players` AS p JOIN `sport`.`gameinstances` AS gi
ON p.`id` = gi.`playerid`                       
WHERE (p.`playerid` IN (CALL findPlayers`("Canada", "2002")))
    AND (gi.`instanceid` NOT IN (CALL findGameInstances`("Canada", "2002")));

For example, the procedures 'findPlayers' and 'findGameInstances' are are stored routines that execute some SQL and return a result set. I would prefer not to include their code directly within the statement above.

Answer 1

Check out this discussion. This answers your questions and gives you some alternatives as well.

Answer 2

I don't know if mysql can use any of these techniques, but in SQl server I would try one of two different things (at least it might give you something to look for in th emysql documentation):

First a table values used defined function then join to that Second, insert the results set of the sp into a temp table then join to the tem table

You could also consider putting the complicated logic in a view and then just adding the where clause after joining to the view. This won't work if your stored proc does dynamic things a view can't do, but it is a possibilty.