Can You Use a Stored Procedure in a Subquery? I Don't Think So. (MySQL)
I do not think you can use the result of a stored procedure in an ad-hoc subquery.
On my social network, LetsGetNuts.com, I have a Friend table. This is the structure:
mysql> describe Friend; +—————-+—————+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +—————-+—————+——+—–+———+——-+ | fkUser | int(11) | YES | MUL | NULL | | | fkFriend | int(11) | YES | MUL | NULL | | | confirmed | enum(‘Y’,‘N’) | YES | MUL | NULL | | | confirmed_date | date | YES | | NULL | | +—————-+—————+——+—–+———+——-+ 4 rows in set (0.02 sec)
The issue here is that building a list of the friends of a certain user involves using a UNION, and I frequently need to use the concept of IN (FRIENDSLIST). I was hoping I could write a stored proc with a union that would record a recordset of userids that I could use in a subquery, kind of like:
SELECT username, firstname, lastname from users where userid in (friendslist());
No such luck, it looks like you can’t do it. I’ve tried a few variations, including call friendslist(), etc… none of them work.
If it’s possible to do this in MySQL I’d be thrilled to know how.
For reference, it’s actually better for me (I think) to just run the stored proc, and saved the comma delimited list on the web server side, instead of running the query against the database several times per page. However, I still would like to know if it’s possible to do the above.
If you found this post helpful, please consider sharing to your network. I'm also available to help you be successful with your distributed systems! Please reach out if you're interested in working with me, and I'll be happy to schedule a free one-hour consultation.