Return to Question

UPDATE: Based on the advice from the commenters, I've found a way of adjusting my SPROC so that I can utilize the TEMP MEMORY table, but be able to explicitly DROP it at the end...

Rather than just calling the SPROC and using the remaining TEMP table to gather the results in the actual query, I've changed the CALL format to use a third OUT variable like so:

... then within the SPROC, I added a second IF tier = 0 at the very end with the following:

So the result of the SPROC is now a comma separated list of ID's that is compatible with FIND_IN_SET, and so the final query was modified so that:

UPDATE: Based on the advice from the commenters, I've found a way of adjusting my stored procedure so that I can utilize the TEMP MEMORY table, but be able to explicitly DROP it at the end...

Rather than just calling the stored procedure and using the remaining TEMP table to gather the results in the actual query, I've changed the CALL format to use a third OUT variable like so:

... then within the stored procedure, I added a second IF tier = 0 at the very end with the following:

So the result of the stored procedure is now a comma separated list of ID's that is compatible with FIND_IN_SET, and so the final query was modified so that:

BEGIN; /* generates the temporary table of ID's */ CALL fetch_inheritance_groups('abc123',0); /* uses the results of the SPROC in the WHERE */ SELECT a.User_ID FROM usr_relationships r INNER JOIN usr_accts a ON a.User_ID = r.User_ID WHERE r.Group_ID = 'abc123' OR r.Group_ID IN (SELECT * FROM id_list) GROUP BY r.User_ID; COMMIT; 

BEGIN; /* generates the temporary table of ID's */ CALL fetch_inheritance_groups('abc123',0); /* uses the results of the stored procedure in the WHERE */ SELECT a.User_ID FROM usr_relationships r INNER JOIN usr_accts a ON a.User_ID = r.User_ID WHERE r.Group_ID = 'abc123' OR r.Group_ID IN (SELECT * FROM id_list) GROUP BY r.User_ID; COMMIT; 

EDIT: To be a little more precise, what if persistent connections are employed, will the table persist through multiple requests? So far it seems that it will and that we would need to explicitly remove the temp table to free up that resource.

EDIT: To be a little more precise, what if persistent connections are employed, will the table persist through multiple requests? So far it seems that it will and that we would need to explicitly remove the temp table to free up that resource.

UPDATE: Based on the advice from the commenters, I've found a way of adjusting my SPROC so that I can utilize the TEMP MEMORY table, but be able to explicitly DROP it at the end...

Rather than just calling the SPROC and using the remaining TEMP table to gather the results in the actual query, I've changed the CALL format to use a third OUT variable like so:

CALL fetch_inheritance_groups('abc123','0',@IDS); 

... then within the SPROC, I added a second IF tier = 0 at the very end with the following:

IF tier = 0 THEN SELECT GROUP_CONCAT(DISTINCT iid SEPARATOR ',') FROM id_list INTO inherited_set; DROP TEMPORARY TABLE IF EXISTS id_list; END IF; 

So the result of the SPROC is now a comma separated list of ID's that is compatible with FIND_IN_SET, and so the final query was modified so that:

WHERE r.Group_ID = 'abc123' OR r.Group_ID IN (SELECT * FROM id_list) 

... is now ...

WHERE r.Group_ID = 'abc123' OR FIND_IN_SET(r.Group_ID,@IDS) 

Voila! Thanks to the commentors for your input, and for giving me the reason I needed to try a little harder :)