Mysql add variable value to result set inside stored procedure instead subquery

This is subquery working fine with required result, now I am writing a stored procedure for the same results.

I want to use in stored procedure logic as

• Declare variables
• Set them
• use in queries

Query:

SELECT payloadstr,
       starttime
FROM   pa
WHERE  conid IN (SELECT conid
                 FROM   con
                 WHERE  lognid IN (SELECT id
                                   FROM   log
                                   WHERE  phyid IN (SELECT id
                                                    FROM   phyid
                                                    WHERE  mac = 9729)));  

Answer

Try this:

BEGIN
    SET @macid = 9729; -- Take input from stored procedure

    SET @phyid_id = (SELECT GROUP_CONCAT(`id`) FROM `phyid` WHERE `mac` = @macid);
    SET @log_id = (SELECT GROUP_CONCAT(`id`) FROM `log` WHERE `phyid` IN(@phyid_id));
    SET @con_conid = (SELECT `conid` FROM `con` WHERE `lognid` IN(@log_id));

    SELECT `str`, `time` FROM `pa` WHERE `conid` IN(@con_conid);
END