Dynamically limit the number of rows returned

Question

I am working on a stored procedure in mariadb and I try to dynamically limit the number of rows. If I hardcode the Limit e.g. LIMIT 5 my procedure works perfectly but if I write LIMIT @below it I receive a quite generic sql syntax error. I can use the variable @below everywhere else in my statement but not after LIMIT. Would be nice to understand this behavior?

SET sql_mode=ORACLE; 
CREATE PROCEDURE hello AS 
BEGIN
DECLARE
below INT;
BEGIN
SELECT round(COUNT(*)/2) FROM tableName INTO @below;

SELECT 
col1,col2, col3, @below /* this is ok*/
FROM tableName 
WHERE col1=@below /* this is ok*/
LIMIT @below; /* doesn't work*/
END;
END hello;

I receive this error:

SQL ERROR(1064): You have an error in your SQL syntax check the manual that corresponds to your MariaDB server version for the right syntax to use near '@below

Thanks Amit

I removed the Oracle tag, since this question doesn't seem to be related to Oracle. — The Impaler, Mar 03 '21 at 17:36
Well... that's pretty common. I've seen the same error in DB2. The engine only accepts parameters at specific locations in the SQL statement. It seems that `LIMIT` is not one of them for this particular version of the engine. The typical solution is to use Dynamic SQL: that is, to assemble the query inside the procedure and then run it, instead of have it prepared already (as you wanted). — The Impaler, Mar 03 '21 at 17:38
It seems it's not possible (as of this version of MariaDB) to use a parameter in that specific location of the query. You'll need to assemble the query dynamically. See https://stackoverflow.com/questions/23178816/mysql-dynamic-query-in-stored-procedure. — The Impaler, Mar 03 '21 at 18:04

score 1 · Answer 1 · answered Mar 03 '21 at 17:52

1

You can achieve this with row_number()over() :

select col1,col2,col3 from(
SELECT 
col1,col2, col3, row_number()over(order by (select 1)) rownumber
FROM tableName ) 
where rownumber<= @below;

answered Mar 03 '21 at 17:52

Kazi Mohammad Ali Nur

13,391
2
11
23

Don't know why but I a syntax error whenever I try using subqueries like in this example. No matter how simple they are, e.g., SELECT col1 FROM (SELECT col1 FROM tableName) – Amit Mar 05 '21 at 15:55
Use a alias like SELECT col1 FROM (SELECT col1 FROM tableName) T – Kazi Mohammad Ali Nur Mar 05 '21 at 16:01

score 1 · Accepted Answer · answered Mar 04 '21 at 10:08

1

Dynamic query can help here:

SET sql_mode=ORACLE; 
CREATE PROCEDURE hello AS 
BEGIN
DECLARE
below INT;
BEGIN
    SELECT ROUND(COUNT(*)/2) FROM tableName INTO @below;
    SET @query = CONCAT('SELECT col1,col2, col3, ? FROM tableName WHERE col1= ? LIMIT ', @below);
    
    PREPARE stmt FROM @query;
    EXECUTE stmt USING @below, @below;
    
    DEALLOCATE PREPARE stmt;
END;
END hello;

answered Mar 04 '21 at 10:08

Slava Rozhnev

8,085
6
21
35

Thanks, this approach works for me if I just do 'EXECUTE stmt;' instead 'EXECUTE stmt USING @below, @below;' – Amit Mar 05 '21 at 13:35

Dynamically limit the number of rows returned

2 Answers2