Mysql stored procedure using cursor fetched variable in sql query

Question

I have defined a following procedure.


create procedure deleteData()
   begin
     DECLARE no_tbls INT;
     DECLARE tbl VARCHAR(64);
     DECLARE tbls_cr CURSOR for SELECT DISTINCT table_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='db';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_tbls=1;
    OPEN tbls_cr;
    SET no_tbls=0;
    while no_tbls=0 do
      fetch tbls_cr into tbl;
      select tbl.updated_at from tbl limit 1;
    end while;
    close tbls_cr;
  end

After running this procedure i am getting an error db.tbl doesn't exist. So i was searching if there is a way to use a cursor fetched object in another query. The problem i am doing all this tedious stuff is that i would like to delete data from all tables of a db with a particular where clause. Note: All tables has a column updated_at with date format. (I am a newbie to MySQL stored procs).

procedure's database name and tables database name are same ? — Vipin Jain
– Vipin Jain, Commented Mar 22, 2016 at 9:16

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You cannot have a variable in place of a table name in the from clause in a plain select statement, mysql will look for a table named tbl in the database.

You need to use string concatenation and prepared statements to dynamically create and execute sql statements:

mysql> USE test;

mysql> CREATE TABLE t1 (a INT NOT NULL);

mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);

mysql> SET @table = 't1';

mysql> SET @s = CONCAT('SELECT * FROM ',@table);

mysql> PREPARE stmt3 FROM @s;

mysql> EXECUTE stmt3;

mysql> DEALLOCATE PREPARE stmt3;

Prepared statements also work in stored procedures, the above example demonstrates how to create the sql statement by concatenating string literals with variables, prepare the statement, execute it, and then release the prepared statement from memory.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 22, 2016 at 9:24

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Swaps Over a year ago

but how can i integrate this in a stored proc? if you can explain this ex. with the context of my ex. it'll be very helpful.. :)

Shadow Over a year ago

I'm sorry, but I do not understand your question! You use the same commands as above, just the variables need to be declared differently, but you already know how to do that.

Swaps Over a year ago

I got you.. I was trying in a same way like @VipinJain answered. ty :)

Shadow Over a year ago

The only difference between my answer and @VipinJain is that he provided a copy-paste code, while I did not. It's easy to provide an answer that requires even less effort from the OP once sy else answered the question.

Swaps Over a year ago

ur right @shadow but before his paste i was trying the same thing he wrote but missing a few things which got cleared by him.

Community · Accepted Answer · 2017-05-23 12:15:25Z

0

Using Prepare Statement you can fetch data dynamically

create procedure deleteData()
   begin
     DECLARE no_tbls INT;
     DECLARE tbl VARCHAR(64);
     DECLARE tbls_cr CURSOR for 
         SELECT DISTINCT table_name 
         FROM INFORMATION_SCHEMA.COLUMNS 
         WHERE table_schema='db';
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_tbls=1;
     OPEN tbls_cr;   
     start_loop : LOOP
      fetch tbls_cr into tbl;
      set @b =  concat('select ', tbl, '.updated_at from ' , tbl, ' limit 1');
      prepare stmt3 from @b;
      execute stmt3;
      DEALLOCATE PREPARE stmt3;          
    END LOOP start_loop;
    close tbls_cr;
  end

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Mar 22, 2016 at 9:47

Vipin Jain

3,77618 silver badges36 bronze badges

3 Comments

Swaps Over a year ago

Cool!! i think its working but where can i see the output? As it is a select query no changes to the DB will take place.. where should i put the final select statement wch will display the output of a select query?

Vipin Jain Over a year ago

this is also show the output, but if you want to put together then you can create a temp table and insert updated_at

Swaps Over a year ago

its working.. i added external select statements to display values of @b & tbl variables. ty :)

Collectives™ on Stack Overflow

Mysql stored procedure using cursor fetched variable in sql query

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related