mysql select query not working with limit and offset set via user variables

Question

I'll be brief:

WORKING

SELECT p.id,
    p.name,
    cat.name `category`,
    prod.name `producer`,
    p.images,
    p.price,
    p.flag_avaliable,
    p.amount,
    p.description,
    p.options
FROM product p
    INNER JOIN product_category cat ON cat.id = p.category_id
    INNER JOIN product_producer prod ON prod.id = p.producer_id
ORDER BY @asc_or_desc
    limit 5 offset 6;

NOT WORKING

set @asc_or_desc = 'id desc ';
set @limit_number = 5;
set @offset_number = 6;
SELECT p.id,
   p.name,
   cat.name  `category`,
   prod.name `producer`,
   p.images,
   p.price,
   p.flag_avaliable,
   p.amount,
   p.description,
   p.options
FROM product p
         INNER JOIN product_category cat ON cat.id = p.category_id
         INNER JOIN product_producer prod ON prod.id = p.producer_id
ORDER BY @asc_or_desc
limit @limit_number offset @offset_number;

So, I need the 'not working' version to work. How can I do that?

I have the same error: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@limit_number offset @offset_number; END''.

Why? Types are ok, you see... What the problem? Maybe values are substitute with some bug?

I'm using JetBrains DataGrip, MySQL 8.0.15.

Use dynamic SQL or built the query in the language of the client if there is any. — sticky bit
– sticky bit, Commented May 4, 2019 at 18:53

Victor Gorban · Accepted Answer · 2019-05-04 19:31:56Z

1

Using prepared statements have solved the problem. The code:

set @statement =
    concat('SELECT p.id,
       p.name,
       cat.name  `category`,
       prod.name `producer`,
       p.images,
       p.price,
       p.flag_avaliable,
       p.amount,
       p.description,
       p.options
FROM product p
         INNER JOIN product_category cat ON cat.id = ?
         INNER JOIN product_producer prod ON prod.id = p.producer_id
ORDER BY id ',@asc_or_desc,'
limit ? offset ?');
prepare prepared from @statement;
EXECUTE prepared USING @category_id, @limit_number, @offset_number;
DEALLOCATE PREPARE prepared;

edited May 4, 2019 at 19:31

answered May 4, 2019 at 19:03

Victor Gorban

1,75722 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Victor Gorban Over a year ago

@GordonLinoff, Yes, It doesn't work. Damn. How to fix it?

Collectives™ on Stack Overflow

mysql select query not working with limit and offset set via user variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related