0

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
average.joe
  • 59
  • 7
  • Use the subquery. I don't see any issue with that. – Gordon Linoff Sep 23 '21 at 11:08
  • @GordonLinoff there were multiple time i will use that subquery. Im thinking it to assign to a variable so if I edit that subquery, i will only just edit once. – average.joe Sep 23 '21 at 11:22
  • does `audit` contain a single row and does this change during the procedure execution? – Stu Sep 23 '21 at 11:27
  • If you want a query to be executed once and reuse the resultset then use temp tables. OR if you are trying to keep the SQL query (in string format ) in a variable, you need to run it dynamically. https://stackoverflow.com/questions/20371677/execute-multiple-semi-colon-separated-query-using-mysql-prepared-statement/20374657#20374657 – techrhl Sep 23 '21 at 11:29
  • It makes no sense. The query will be parsed and executed each time when you use it. If you want to reuse its output then store it into temporary table. – Akina Sep 23 '21 at 11:41

1 Answers1

0

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;
KaeM
  • 225
  • 3
  • 6