3

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.

Arman
  • 4,566
  • 10
  • 45
  • 66

1 Answers1

1

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.

Community
  • 1
  • 1
Orbling
  • 20,413
  • 3
  • 53
  • 64
  • @Arman Quite alright, I work in SQL Server and MySQL a lot. Dynamic SQL is very commonly found within stored procedures in SQL Server, but not so much in MySQL. Whether it is a sensible approach or not is a matter of opinion. ;-) – Orbling Nov 24 '10 at 16:46
  • I like to call from *.cs nice stored procedures. recently started to port the web to LAMP, I just need o change the sql dialect. I wondered why mysql people does not like to push some functions to MySql, maybe phpmyadmin does not show them:). – Arman Nov 26 '10 at 11:47
  • @Arman I think it is because of the hosting situation. Many web hosts have only recently upgraded to MySQL 5, after years of sticking to MySQL 4. MySQL 4 does not have stored procedures or views, but obviously MySQL 5 is a far more complete offering and rival to SQL Server/Oracle/etc. Web developers, who are amongst the primary users of MySQL, have to cater for the lowest common denominator, otherwise their software is incompatible with certain hosts. – Orbling Nov 26 '10 at 18:07