Assign Query String in a variable

Question

In mySQL stored procedure how can I assign a Query String to a variable, so I can reuse it? In my example I will be using SELECT id FROM audit many times.

CREATE PROCEDURE my_proc() 
BEGIN

UPDATE person SET status='Active' WHERE id = (SELECT id FROM audit);

SELECT COUNT(*) FROM (SELECT id FROM audit);

//Multile scenarios the `SELECT id FROM audit` will be used.

END

Something like:

CREATE PROCEDURE my_proc() 
BEGIN
myVariable = SELECT id FROM audit;
UPDATE person SET status='Active' WHERE id = (myVariable;

SELECT COUNT(*) FROM (myVariable);

//Multile scenarios the `SELECT id FROM audit` will be used.

END

Answer 1

Is this what you are looking for? Sorry I am not sure what you need.

    SELECT @myCount:= count(id)  FROM audit;
    select @myCount;

Based on your reply, do you need a temporary table to store the ids from the audit and re-use those on the queries?

create temporary table tbl_tmp_audit;
select id from audit; 

I am assuming you need this so that you won't join the whole audit columns every time on your succeeding queries.

--first query
UPDATE person AS p
INNER JOIN tbl_tmp_audit t ON p.id = t.id
SET status = 'Active';
--second query
SELECT COUNT(*) FROM tbl_tmp_audit;

Drop temporary table tbl_temp_bookings;