Re: Dynamic SQL. Is it possible?

[Roland Bouman]

I'm glad I could help out.

Now, as for the prepared statements, there are a couple of things that are you should take into account. (I'm certainly not advising you to use it if you can avoid it, but if you really need it, here's how to do it)

1) make sure you're running 5.0.13 or later. Earlier versions have no or limited support
2) usage is allowed from inside stored procedures only; NOT from inside functions or triggers (If the SP is called from inside a trigger or function, it isn't allowed either).
3) you can only either a string literal or a "global user variable", a @ variable to specify the statement. You can *NOT* use an expression, or a DECLAREd stored procedure variable. This means that if you do need an expression to build a statement dynamically, you must first assign the result of the expression to a user variable, and then use that in the PREPARE syntax:

PREPARE stmt FROM 'SELECT * FROM product';

or

DECLARE v_order_by varchar(64) default 'name';
set @sql_text := 'SELECT * FROM product';
set @sql_text := CONCAT(@sql_text,'\n','ORDER BY ',v_order_by);
PREPARE stmt FROM @sql_text;

but not:

DECLARE sql_text text;
DECLARE v_order_by varchar(64) default 'name';
set sql_text := 'SELECT * FROM product';
set sql_text := CONCAT(sql_text,'\n','ORDER BY ',v_order_by);
PREPARE stmt FROM sql_text;

4) Parameters in the statement are represented by ? placeholders. When EXECUTE-ing the statement, you must bind values with the USING syntax, passing the parameter values as global user variables or @ variables:

set @order_by1 := 'category';
set @order_by2 := 'name';
set @sql_text := 'SELECT * FROM product ORDER BY ?,?';
PREPARE stmt FROM @sql_text;

EXECUTE stmt
USING @order_by1
, @order_by2
;

5) Parameter placeholders are allowed only in those places where you could use an expression. So, you cannot parameterize an identifier with a parameter. Therefore, the following is wrong:

PREPARE 'create table ? (id int)';

If you do need to do this, revert to ordinary string manipulation:

set @table_name = 'mytable';
set @sql = concat('create table ',@table_name,' (id int)');

6) The types of statements you can execute are limited. I know you can do: CREATE TABLE, INSERT, SELECT, UPDATE, DELETE. I know you can't do: CREATE VIEW, CREATE PROCEDURE. I haven't checked the rest.


Final note on this: be advised that the prepared statement syntax is exactly what it is called: a syntax that let's you *prepare statements*. This concept has nothing to do with dynamic sql; it's just that the particular syntax used to prepare statements also allows you to build SQL statements dynamically.

The reverse is true too. I dare say that dynamic SQL is about the most widespread way of feeding the MySQL server it's SQL. It's just that most people build their statements outside the MySQL context, e.g. in a PHP script:

$sql = "SELECT * FROM product";
if (isset($_REQUEST["orderby"])){
$sql .= "\nORDER BY ".$_REQUEST["orderby"];
}
$rst = mysql_query($sql);

So, here we have dynamic sql, that is not prepared at all.