MySQL Forums
Forum List  »  Stored Procedures

Re: Dynamic SQL. Is it possible?
Posted by: Roland Bouman
Date: November 04, 2005 03:40PM

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';


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;

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.

Options: ReplyQuote

Written By
November 04, 2005 01:40AM
November 04, 2005 03:51AM
November 04, 2005 08:51AM
November 23, 2005 10:50AM
November 23, 2005 02:18PM
November 04, 2005 08:54AM
Re: Dynamic SQL. Is it possible?
November 04, 2005 03:40PM
November 04, 2005 05:21PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.