MySQL Stored Procedure | How to write it?
This time I have a MySQL question, I'm trying to create a stored procedure which will execute a prepared statement, the goal is to get a ranged list from a table("`order_info`"), the list is divided by "pages", each page is determined by a record count and should be ordered using a particular field sorted either `'ASC'` or `'DESC'`, each record represents an "`order`" the catch here is that the procedure returns the orders of a particular group, the the order is associated to a `user` which belongs to a `group`. Here's what I've done so far:  
<pre><code>
    CREATE DEFINER=`root`@`%` PROCEDURE `getGroupOrders`(IN grp INT,
                                                   IN page INT, 
                                                   IN count INT, 
                                                   IN ord VARCHAR(10), 
                                                   IN srt VARCHAR(4)
                                                   )
    BEGIN
        PREPARE prepGroupOrders FROM
        "SELECT * FROM `dbre`.`order_info` 
            WHERE username IN (
                           SELECT `dbre`.`users`.`username` FROM `dbre`.`users` 
                           WHERE `dbre`.`users`.`id_group` = ?
                           ) 
            ORDER BY ? ? LIMIT ?, ?";
        
        SET @g := grp;
        SET @cnt := count;
        SET @start := @page*count ;
        SET @orderBy := ord;
        SET @sortBy := srt;

        EXECUTE prepGroupOrders USING @g,@orderBy,@sortBy,@start,@cnt;
    END
</code></pre>
I get a syntax error when executing this, even though the editor does not higlight any errors and lets me save the procedure,I think that one of the follwing may be happening:
<ul>
<li> I am incorrectly usng the `ASC` or `DESC` since it is a SQL reserved word.</li>
<li> I read somewhere that Prepared statement are for only ONE SQL query, and since I have nested queries it can't be done.</li>
</ul>

I've tested this standard query:  
<pre><code>SELECT * FROM `dbre`.`order_info` 
    WHERE username IN (
                       SELECT `dbre`.`users`.`username` FROM `dbre`.`users` 
                       WHERE `dbre`.`users`.`id_group` = 1
                       ) 
    ORDER BY `status` DESC LIMIT 5, 10;</code></pre>
And it gives me the results I want. SO how would I design the procedure?
Any help is truly appreciated.

MySQL Stored Procedure | How to write it?
===

This time I have a MySQL question, I'm trying to create a stored procedure which will execute a prepared statement, the goal is to get a ranged list from a table("`order_info`"), the list is divided by "pages", each page is determined by a record count and should be ordered using a particular field sorted either `'ASC'` or `'DESC'`, each record represents an "`order`" the catch here is that the procedure returns the orders of a particular group, the the order is associated to a `user` which belongs to a `group`. Here's what I've done so far:  
<pre><code>
    CREATE DEFINER=`root`@`%` PROCEDURE `getGroupOrders`(IN grp INT,
                                                   IN page INT, 
                                                   IN count INT, 
                                                   IN ord VARCHAR(10), 
                                                   IN srt VARCHAR(4)
                                                   )
    BEGIN
        PREPARE prepGroupOrders FROM
        "SELECT * FROM `dbre`.`order_info` 
            WHERE username IN (
                           SELECT `dbre`.`users`.`username` FROM `dbre`.`users` 
                           WHERE `dbre`.`users`.`id_group` = ?
                           ) 
            ORDER BY ? ? LIMIT ?, ?";
        
        SET @g := grp;
        SET @cnt := count;
        SET @start := @page*count ;
        SET @orderBy := ord;
        SET @sortBy := srt;

        EXECUTE prepGroupOrders USING @g,@orderBy,@sortBy,@start,@cnt;
    END
</code></pre>
I get a syntax error when executing this, even though the editor does not higlight any errors and lets me save the procedure,I think that one of the follwing may be happening:
<ul>
<li> I am incorrectly usng the `ASC` or `DESC` since it is a SQL reserved word.</li>
<li> I read somewhere that Prepared statement are for only ONE SQL query, and since I have nested queries it can't be done.</li>
</ul>

I've tested this standard query:  
<pre><code>SELECT * FROM `dbre`.`order_info` 
    WHERE username IN (
                       SELECT `dbre`.`users`.`username` FROM `dbre`.`users` 
                       WHERE `dbre`.`users`.`id_group` = 1
                       ) 
    ORDER BY `status` DESC LIMIT 5, 10;</code></pre>
And it gives me the results I want. SO how would I design the procedure?
Any help is truly appreciated.
