0

I have a follow up question on the link below. Apparently I have to create a new question (Sorry if I should not, I am new to stackoverflow)

One of the responses to the issue was this:

SELECT sql_text INTO @sql FROM sql_queries WHERE id = 1;
PREPARE sql_query FROM @sql;
EXECUTE sql_query;

It helped if I run the above line by line, but I want to run this as a whole and use the result in another query. So my issue is the following:

1 - It doesn't work if I remove the semicolons and try to run the three lines at the same time.

2 - How can I read the results of this run into a query. I was thinking of putting all three lines in a subquery, but that didn't work.

I appreciate if you have any comment on those. Thanks!

OriginalQuestion

Baran
  • 1
  • these are three different SQL statements. So the semicolon is not an option – Lelio Faieta Sep 20 '18 at 16:30
  • From what I recall, the only way you can use the results of a PREPARE is to have the query that was PREPAREd insert into a table you can select from after the EXECUTE. – Uueerdo Sep 20 '18 at 16:47
  • can you post these three lines ? I want to see the result of selecting the lines – Amazigh.Ca Sep 20 '18 at 16:52
  • I guess my question was a bit confusing. You can see the original question in the link I posted. If I run these three lines one by one it would run the query that is saved in CT. But if I remove the semicolons it gives me error! My final goal is to read some queries from a table (like REPORT_QUERY field in CT in this example) and run them and use the result of that in another query. – Baran Sep 20 '18 at 17:02

1 Answers1

0

I do not think you can "embed" an execute (nor it's results) in another query, which is what it sounds like what your goal is. Instead you might want to consider building your final query with the @sql query as a subquery like:

SET @finalSQL = CONCAT("SELECT stuff FROM sometables JOIN (", @sql, ") AS subQ ON blah");
PREPARE sql_query FROM @sql;
EXECUTE sql_query;

Of course, you need to make a lot of assumptions about the @sql query you've been provided; at minimum you must assume: it will be valid for the join conditions (or however the subquery is used) and that it is not terminated with a semi-colon. The latter can be easily checked and remedied, the former could require a significant amount of parsing and analysis.

Uueerdo
  • 15,723
  • 1
  • 16
  • 21
  • Thanks Uueerdo! You are right. That's what I want to do. Your suggestion can solve the issue. But I should make preparation on the rest of the code to be able to use it. Let me work on that and see how I can use this information. – Baran Sep 20 '18 at 17:32