i can not make aritmetic operation inside stored procedure mysql

Question

there is an in parameter as page. all i want to do is expel 1 from it and multiply by 10. but it gives me an error every time.

IF !a THEN

  SELECT * 
  from entry 
  WHERE topic_foreign_id = ( 
    select topic_id 
    from topic 
    where topic_name = topicName
  )
  ORDER BY entry_time ASC 
  LIMIT 10 OFFSET page;

ELSE

  SELECT * 
  from entry
  WHERE topic_foreign_id = ( 
    select topic_id 
    from topic 
    where topic_name = topicName
  )
  ORDER BY entry_time ASC;

END IF

this lines of code works great but when i want to make an aritmetic operation in first SQL query myAdmin throws an error everytime.

SELECT * 
from entry 
WHERE topic_foreign_id = ( 
  select topic_id 
  from topic 
  where topic_name = topicName
) 
ORDER BY entry_time ASC 
LIMIT 10 OFFSET 10 * ( page - 1); //throws error

@AlexK. look end of second code line: 10 * ( page - 1) is the cause of error. — Gai Kuroda, Sep 13 '17 at 13:37
Refer https://stackoverflow.com/questions/300697/using-variables-as-offset-in-select-statments-inside-mysqls-stored-functions — Kaushik Nayak, Sep 13 '17 at 13:56

Jacques Amar · Answer 1 · 2017-09-13T17:14:09.920

0

--- EDITED ---

LIMIT doesn't seem to like calculations. Try using a variable and use that value in your stored procedure

DECLARE v_page_offset INT DEFAULT 0;
SET v_page_offset = 10*(page-1);

Then later

LIMIT 10 OFFSET v_page_offset;

edited Sep 13 '17 at 17:14

answered Sep 13 '17 at 16:56

Jacques Amar

1,803
1
10
12

i can not make aritmetic operation inside stored procedure mysql

1 Answers1