CODEWITHSUNDEEP
mysql
vivek
vivek

Reputation: 1605

How to run mysql queries that are stored in a mysql table using a procedure

I am developing a simple WPF(C#) application where i am storing all insert, update and delete queries in a table. These queries are then executed on server one by one by simply selecting and then executing using ExecuteNonQuery() function in c#. The problem is that if have a large number of queries then it becomes very slow and sometimes due to network connection it is falling out. Is it possible to create a stored procedure that can execute the queries stored within a table on the same server?

Here is some code that I have tried till now.

DELIMITER $$
CREATE PROCEDURE  `MyProc`(wo varchar(100))
BEGIN
              DECLARE x  INT;
              DECLARE str  text;
              set x = 0;
              select count(*) into x from tblqueries where isexecutedonserver  = false and woid = wo;
              SET str =  '';
              REPEAT
                          select `query` into str from tblqueries where id = 2976;
                          SET  x = x - 1;
              UNTIL x  > 0
              END REPEAT;
              ##select str;
              prepare stmt from @str;
              execute stmt;
              deallocate prepare stmt;
      END $$
DELIMITER ;

Upvotes: 0

Views: 2227

Answers (2)

vulkanino
vulkanino

Reputation: 9134

This is simple (as simple as googling "mysql stored procedure execute")

Declare a CURSOR in your stored procedure. Execute the cursor, then prepare a statement with the output:

PREPARE stmt FROM @sql;
EXECUTE stmt USING @myvar;

This code will help you with it: MySQL Pass table name to cursor select

Upvotes: 3

Paul Nikonowicz
Paul Nikonowicz

Reputation: 3903

It sounds like what you need is a view.

Views (including updatable views) are available in MySQL Server 5.0. Views are stored queries that when invoked produce a result set. A view acts as a virtual table. Views are available in binary releases from 5.0.1 and up.

http://dev.mysql.com/doc/refman/5.0/en/views.html

Upvotes: 1

Related Questions