CODEWITHSUNDEEP
mysqlstored-procedures
Psymon
Psymon

Reputation: 17

Using parameter in stored procedure to get dynamic table name

I already wrote a pretty complex stored procedure, but now i want to bring some dynamics into the procedures using variables. I still call myself a newbiew when it comes to sql programming, so i might be totally on the wrong way with what i'd like to achieve. I am running the latest MySQL server 8.0.13 on windows (just for local dev).

My current idea for the stored procedure
... uses an input variable
... declares a cursor (using the var)
... create a temp table (using the var)
... iterates through the temp table
... write data to a new table

I think i understand some of the basic tools to do that but i fail again and again with handling the variable(s) The following code shows the top part of the project where i already fail.


 PROCEDURE sprProcedure (
  IN vInputParameter varchar(64)
 )
 BEGIN
  DECLARE vFinished INTEGER DEFAULT 0;

  -- declare cursor
  DECLARE cCursor CURSOR FOR SELECT * FROM tbltmp_(vInputParameter);
  DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET vFinished = 1;

  -- drop / create temp table
  DROP TABLE IF EXISTS (vInputParameter);
  CREATE TEMPORARY TABLE tbltmp_(vInputParameter)
   SELECT * FROM `tbl_DataSource`;

... did not paste any further code as i can't deal with the following issue.

I tried
... using the variable directly > variable is not resolved in DECLARE CURSOR statement
... define user-defined variable before local-variables is not allowed
... CONCAT'ed the string to build a statement and EXECUTE'ed it > does not work in the DECLAREpart of the stored procedure

At this point i am totally open to every hint or totally new ideas about how to approach this.

Thanks for any advice!

[Update]
My approach up there has some flaws and i did not understand the basic concept of temp tables for example being unique to the session. Thanks to 'D's Query' and a little more reading i now have a working stored procedure without having to deal with dynamic names of cursors and temp tables.

Upvotes: 0

Views: 924

Answers (1)

D's Query
D's Query

Reputation: 71

I dont know what actually you are focusing on but loading a dynamic tables can be possible with prepared statements in Stored Procedures but prepared statements won't work with CURSORS.

Example of Prepared statement is 

SET $sql=CONCAT('SELECT * from ', var_tablename);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 1

Related Questions