Skip to content
Advertisement

Using arithmetic operators and/or brackets in a LIMIT clause

I have the following small subquery in query in stored procedure.

(select f_cnt from results limit (i-1)*10,i*10)

But there is the syntax error:

“(” is not valid at this position, expecting an identifier

So, the question is: do I have a possibility to use brackets and/or arithmetic operators in LIMIT clause?

Documentation says I can use local variables in LIMIT clause within stored procedure. Do I really need to declare and set different variables for this case?

Just in case, link for the code of stored procedure.

Advertisement

Answer

You can’t do arithmetic at that point

So do instead

SET @sql := CONCAT("SELECT * FROM TEsttable WHERE id In(select f_cnt from results limit ",(i-1) * 10,",",i*10,")");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Which will produce

SELECT * FROM TEsttable WHERE id In(select f_cnt from results limit 90,100)

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement