Using arithmetic operators and/or brackets in a LIMIT clause

Question

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.

nbk · Accepted Answer · 2020-11-15 17:55:30Z

2

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)

answered Nov 15, 2020 at 17:55

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

3 Comments

Gennadiy Over a year ago

ehm... Ok, I think I understood what kind of mechanics you're suggesting. But I don't yet know how these statements work. Actually, my topic is not an issue, because I've passed through syntax error with two additional variables. Still, the question about LIMIT clause is open.

nbk Over a year ago

@Gennadiy prepared statemenets like this work like any other prepared statement, you can add to the query where you want data a ? and add to the execute part the variables see dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html only Limit and offet doesn't work that way, it has to be done like teh answer

Gennadiy Over a year ago

it looks like useful approach, I'll learn deeper. Thanks

Collectives™ on Stack Overflow

Using arithmetic operators and/or brackets in a LIMIT clause

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related