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
– The Impaler, Commented Mar 3, 2021 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
– The Impaler, Commented Mar 3, 2021 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 stackoverflow.com/questions/23178816/…. — The Impaler
– The Impaler, Commented Mar 3, 2021 at 18:04

Slava Rozhnev · Accepted Answer · 2021-03-04 10:08:11Z

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 4, 2021 at 10:08

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

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

1 Comment

Amit Over a year ago

Thanks, this approach works for me if I just do 'EXECUTE stmt;' instead 'EXECUTE stmt USING @below, @below;'

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-03-03 17:52:46Z

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 3, 2021 at 17:52

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

2 Comments

Amit Over a year ago

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)

Kazi Mohammad Ali Nur Romel Over a year ago

Use a alias like SELECT col1 FROM (SELECT col1 FROM tableName) T

Collectives™ on Stack Overflow

Dynamically limit the number of rows returned

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related