Using DBFiddle, I create a table
create table t(x int);
insert into t values(1),(2),(3);
LIMIT works
select * from t limit 1,1;
returns x|2
It is documented that user-defined variables are not allowed.
User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement
so
set @s=1;
select * from t limit @s,1;
produces an error:
Query Error: Error: ER_PARSE_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 '@s,1' at line 1
However, if I create a procedure
delimiter //;
create procedure lmt(s int)
begin
select * from t limit s,1;
end;
call lmt(@s);
it appears that parameters are accepted, so what is the difference between a user-variable and a procedure parameter that allows this?
