How to send a request to a stored procedure in MySQL?

Question

Hello, I am trying to automate my history tracking procedure in MySQL. The procedure should update the table and create another, using uid as the 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 name:

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

What is the correct way to use the varchar variable in a procedure?

Thanks in advance. Arman.

Answer 1

I think Dynamic SQL could be used with this.

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

Sort of:

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;

, '?' CONCAT(), , .

, SO post.