Actually you can do it, it is tricky though. Below is my example, I
have used similar in many procedures when I want to variabl-ize (made up
word I know) table names. The same holds true for setting field values
or order/group by.
delimiter //
DROP PROCEDURE IF EXISTS TestSelect
//
CREATE procedure TestSelect(IN field1 INT)
BEGIN
SET @QueryStmt = CONCAT(
" SELECT * FROM testTable WHERE X=1412 GROUP BY ",
field1
);
PREPARE stmt FROM @QueryStmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
//
Hope this helps.
David Norman
Wells Fargo Services
This message may contain confidential and/or privileged information. If
you are not the addressee or authorized to receive this for the
addressee, you must not use, copy, disclose, or take any action based on
this message or any information herein. If you have received this
message in error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.
-----Original Message-----
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Friday, April 08, 2005 4:46 PM
To: Paul Beer; mysql@lists.mysql.com
Subject: Re: stored procedure
At 17:15 -0400 4/8/05, Paul Beer wrote:
>I'm trying to pass a value into a stored procedure to dynamically set
>which column(s) to sort by. The following code doesn't work.
>The query executes but ignores my order by parameter.
>I assume there is a simple answer to this that I'm just missing.
Yes. Unfortunately, the simple answer is that you can't do it.
Parameters are for data values, not column names.
(You'll encounter a similar problem in many database APIs if you try to
use a parameter in a prepared statement for anything but a data value.)
>
>create procedure sp_equipment_find (
>IN L_ORDER_BY MEDIUMTEXT
>)
>
>BEGIN
>SELECT * from mytable ORDER BY @L_ORDER_BY; END$
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
