开发者

SQL syntax as parameters for a MySQL Routine?

开发者 https://www.devze.com 2023-01-30 14:22 出处:网络
I have the following MySQL routine: DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `getGroupOrders`(grp INT,

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

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号