MySQL Forums
Forum List  »  Stored Procedures

Re: Stored Procedure with a "ORDER BY" parameter
Posted by: Roland Bouman
Date: November 09, 2005 05:21PM

It's not that simple. The ? not so much substituted; it's more like the ? is a variable.
in a statement like:

EXECUTE stmt_handle USING @sorder

, the value of @sorder is bound to that variable. There really is a distinction between binding the value and substituting it. Consider this:

CREATE TABLE mytab(col1 char(1), col2 char(1));

PREPARE stmt_handle
FROM 'insert into mytab (col1, col2) values(?,\'?\')';

Now, the EXECUTE, how many parameters does it accept? 1 or 2?
Suppose it would be substitution, and do this:

set @col1 := 'a';
set @col2 := 'b';
EXECUTE stmt_handle USING @col1,@col2;

Now, the substituted statement would look like this:

insert into mytab (col1, col2) values(a,'b');

Well, this would lead to an syntax error, because the a is not quoted...if it was indeed a substitution (it would still be a syntax error if we would insert the quotes areound 'a' and 'b' too during substitution).

We know it is not. It's binding. That's why for

'insert into mytab (col1, col2) values(?,\'?\')'

only the first ? is recognized as parameter. The second one is just a string. We cannot even call execute using more than one parameter: there is only parameter here.

Once we accept that it is a variable, it's easy to see why you cannot plug in an identifier. If you want to do that, you are just going to have to use concat and build your string using that like my correction (my previous post)



Edited 1 time(s). Last edit at 11/09/2005 05:23PM by Roland Bouman.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Stored Procedure with a "ORDER BY" parameter
8000
November 09, 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.