How do you build and use dynamic sql in a MySQL stored procedure?
+5
A:
I don't believe MySQL supports dynamic sql. You can do "prepared" statements which is similar, but different.
Here is an example:
mysql> PREPARE stmt FROM
-> 'select count(*)
-> from information_schema.schemata
-> where schema_name = ? or schema_name = ?'
;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt
-> USING @schema1,@schema2
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
The prepared statements are often used to see an execution plan for a given query. Since they are executed with the execute command and the sql can be assigned to a variable you can approximate the some of the same behavior as dynamic sql.
Here is a good link about this:
http://rpbouman.blogspot.com/2005/11/mysql-5-prepared-statement-syntax-and.html
Good Luck!
Jason Stevenson
2008-10-10 11:56:47
it isn't working when the `?` is in the place of the table name
Jader Dias
2010-01-11 19:50:04
Are you trying this via the mysql console? or using another method, if you are using this in code, your provider would need to support it.
Jason Stevenson
2010-01-11 20:53:46