Re: Stored Procedure with a "ORDER BY" parameter
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.