How can I define separate temporary table source name in a procedure?

Question

I'm declaring a cursor in a stored procedure with following;

declare cur1 cursor for select * from tmp_01;  

Here, my temporary table source is tmp_01.

The source table name is dynamically generated.

I'm wondering if there is a way that I could define the same cursor with different source for each instance when the stored procedure called.

For example,

on first run,

  declare cur1 cursor for select * from tmp_01;  

on second run,

declare cur1 cursor for select * from tmp_02;  

The main problem I'm having is, I'm experiencing some strange behavior with the cursor when called with multiple queries using mysqli_multiquery, that is not clear to me. when I run each query separately, everything works fine. I'm not sure whether it's because something like parallel query processing.

All I'm trying to achieve is, declaring a unique source name for the cursor, on each procedure call.

Can anyone please point me in a right direction to achieve this?

Answer 1

No, the DECLARE CURSOR statement must take a fixed SQL query as its argument, and therefore the table name must be fixed. If your table name is variable, you cannot use a cursor in a stored routine.

It's not clear from your question what purpose you have for using multiquery, or what is the "strange behavior" you have seen. I can guess that it has to do with the fact that each call to a stored procedure returns multiple result sets, so it gets confusing if you try to call multiple procedures in a multiquery. If you are looping over multiple result sets, it becomes unclear when one procedure is done with its result sets and the next procedure starts returning its result sets.

Regardless, I don't recommend using multiquery in any case. There is hardly ever a good reason to use it. There's no performance or functionality advantage of using multiquery. I recommend you just run each call individually, and do not use multiquery.

For that matter, I also avoid using MySQL stored procedures. They have poor performance and scalability, the code is harder to write than any other programming languages, there is no compiler, no debugger, no support for packages, no standard library of utility procedures, the documentation is thin, etc. I understand that in the Oracle or Microsoft SQL Server community, it is customary to write lots of stored procedures, but in MySQL, I write my application logic in a client programming language such as Java, Go, or Python.