How to submit the query by stored procedure in MySQL?

Hello I am trying to automate my history tracking procedure in MySQL. The procedure should update a table and create another using uid as a name.

CREATE PROCEDURE `InsertQueryStore`( u VARCHAR(128), ID INT, 开发者_运维问答q VARCHAR(1024) )
BEGIN   
  INSERT INTO querystore(`qID`, `qstring`, `user`) VALUES(ID, q, u); # this works 
# DROP TABLE IF EXIST ID ; //Can I do something like this?
# CREATE TABLE ID q; // The q is a query string which should return results into to table ID
END;

then I would like to call as:

Call InsertQueryStore("myname", 100, "select * from mydb.table limit 10")

What is the proper way to use the varchar variable in the procedure?

Thank you beforehand. Arman.

I think the way to go with that would be using Dynamic SQL.

MySQL does not support dynamic SQL in the way some DBMS do, but it does have the PREPARE/EXECUTE methods for creating a query and executing it. See if you can use them within your stored procedure.

Something like:

CREATE PROCEDURE `InsertQueryStore`( u VARCHAR(128), ID INT, q VARCHAR(1024) )
BEGIN   
  INSERT INTO querystore(`qID`, `qstring`, `user`) VALUES(ID, q, u);

  PREPARE stmt FROM "DROP TABLE IF EXIST ?";
  EXECUTE stmt USING ID;
  DEALLOCATE PREPARE stmt;

  /* etc */
END;

If you find you can't use the parameterised version with '?' in that context, just use CONCAT() to assemble it with the actual value in the string as it is already known at that stage.

There is a reasonable article about it here, mentioned in a previous SO post.