tags:

views:

4062

answers:

1

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
it isn't working when the `?` is in the place of the table name
Jader Dias
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