I have the following MySQL routine:
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `getGroupOrders`(grp INT,
ord CHAR(20),
srt CHAR(4),
page INT,
count INT)
BEGIN
SELECT *
FROM `dbre`开发者_Python百科.`order_info`
WHERE username IN (SELECT `dbre`.`users`.`username`
FROM `dbre`.`users`
WHERE `dbre`.`users`.`id_group` = grp)
ORDER BY ord srt LIMIT page,count;
END
As you can see, I want to pass the ordering column and sorting as a parameters, however I get a syntax error is there a way to do this or do I have to make similar routines for each type of ordering?
I don't think this is possible in the way you try it. You cannot use a variable to define the ORDER BY column an direction.
The only workaround I can think of is to create a prepared statement from a dynamically created string (where you can use the variables to specify the order by details) and then execute that prepared statement.
Here is an example of such a dynamic statement: http://forums.mysql.com/read.php?98,393613,393642#msg-393642
精彩评论