Set Result Limit By Variable and Error When Declaring Variable

Question

I want to set the limit of shown result from 0 until the value of declared variable. But I found the error when declaring the variable, it said : "#1064 - you have an error in your SQL syntax.". I know that DECLARE syntax only permitted in the BEGIN ... END statement. But why I always get an error?

This is what I've try :

BEGIN
DECLARE panjang INT DEFAULT 0
END;

SET panjang := 10;
SELECT * FROM `TABLENAME`
limit 0, panjang

Can you please tell me what is missing here? I'm using mysql version 5.5.34.

Rick James · Accepted Answer · 2015-02-23 04:59:20Z

1

DECLARE is allowed only in Stored Routines.
Inside a stored routine, you can construct a SELECT with a variable for LIMIT; this uses CONCAT, PREPARE, and EXECUTE.

If this SELECT is coming from some other programming language (PHP, VB, Java, etc), I suggest you build the SQL there, so you won't have to learn about Stored Procedures.

answered Feb 23, 2015 at 4:59

Rick James

144k15 gold badges144 silver badges255 bronze badges

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

2 Comments

Oki E. Rinaldi Over a year ago

so, I have to create procedure right? or do you know how to set limit like ` ... LIMIT 0,(Select count(*) from tablename2)` ?

Rick James Over a year ago

It takes 10-20 lines of code to do what you are asking for. Do you really need it?

Collectives™ on Stack Overflow

Set Result Limit By Variable and Error When Declaring Variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related