CODEWITHSUNDEEP
mysqlstored-procedures
roflcopter1101
roflcopter1101

Reputation: 333

How to execute a stored statement in a stored procedure

I'm creating a function in mySQL that will update table metrics each time the any table has something inserted or deleted from it. So I've got my Table_metrics table, with elements table_name and row_count: 

    CREATE TABLE Table_metrics (
        table_name VARCHAR(64), 
        row_count INTEGER DEFAULT 0 );

So any time something gets added to any of my tables (other than this one) in the database the matching row gets updated with the number of rows in that table.

To do this I've tried making a stored procedure: 

    CREATE PROCEDURE table_update_metric(IN tablename VARCHAR(64))
        UPDATE Table_metrics 
            SET row_count=(SELECT COUNT(*) FROM tablename) 
        WHERE table_name = tablename;

This produces an error when I call the procedure call table_update_metric('Owners'); (Owners being a table in my database) 

    table DB.tablename doesn't exist

I've done a bit of digging into stored procedures, trying to figure out how they would work. I assume the issue is coming from the line `SELECT COUNT(*) FROM tablename), so I tried having a stored statement in the procedure: 

    CREATE PROCEDURE table_update_metric(IN TABLENAME VARCHAR(64))
        SET @s = CONCAT('SELECT COUNT(*) FROM ', tablename)
        UPDATE Table_metrics 
            SET row_count=EXECUTE @s 
        WHERE table_name = tablename;

I'm not really sure how to properly do a stored statement as I'm still relatively new to mySQL, but I believe that it's the way to go.

Can anyone offer any insight into this problem?

Upvotes: 1

Views: 49

Answers (2)

roflcopter1101
roflcopter1101

Reputation: 333

From what Somonare said, I created a table in my database that uses that information: 

    CREATE TABLE Table_metrics 
        SELECT TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'Sensors';

And this table will be updated with a trigger each time the tables in Sensors are updated.

Upvotes: 0

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30663

What you are trying to do is already provided in Mysql. Information Schema Tables stores this information for you :)

 SELECT TABLE_NAME, TABLE_ROWS)
 FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_SCHEMA = '{your_db}';

Upvotes: 1

Related Questions