MySQL - Using the result of a Stored Procedure in an Inner Join

Question

I have a MySQL Stored Procedure that returns multiple rows. Is there a way to use this result in an Inner Join with another table? I've tried:

SELECT ErrorMessage FROM ErrorMessage em 
INNER JOIN User_Language(pCompanyID, pUserID) l ON em.Language=l.LanguageID 
WHERE ErrorCode = pErrorCode 
ORDER BY l.LanguageOrder LIMIT 1;

In this example, User_Language is the stored procedure that returns a list of languages in order of preference. The intent is to return an error message in the user's preferred language.

Answer 1

I found a viable solution. Within the User_Language stored proc, I create a Temp table (called UserLanguages) which I can then use in the inner join.

CALL User_Language(pCompanyID, pUserID);
SELECT ErrorMessage INTO vErrorMessage FROM ErrorMessage em
INNER JOIN UserLanguages l ON em.Language=l.LanguageID
WHERE ErrorCode = pErrorCode
ORDER BY l.LanguageOrder LIMIT 1;