0

I'm writing a script I'd like to be able to run as I please. Unfortunately, the SQL is very long and I'd like to be able to change a couple things. However, I'm unable to access a variable in the script.

Here's an example:

SET @MaxResults = 3;
SELECT a, b, c FROM ...<lots of content>... LIMIT @MaxResults;

For some reason this is a syntax error and I can't understand why. What's the best way to define a variable in MariaDB?

awy
  • 43
  • 6

2 Answers2

1

You can't use user-defined variables as the argument for LIMIT.

The documentation for LIMIT in the MariaDB knowledgebase does not make this totally clear.

In the MySQL manual, it is more clear:

LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

  • Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

  • Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

Because user-defined variables (the kind with the @ sigil) are not typed, they are effectively strings. Strings are not allowed as arguments to LIMIT.

The same restrictions apply to MariaDB. MySQL and MariaDB are not always compatible, but in this case their behavior is the same.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

User-defined variables in MySQL

MySQL recognizes different types of variables. The first type is user-defined variables, identified by an @ symbol used as a prefix. In MySQL, user-defined variables can be accessed without first declaring or initializing them. If you do so, a NULL value is assigned to the variable when it is initialized.

Declaring local variables in MySQL

Local variables do not need the @ prefix in their names, but they must be declared before they can be used. To declare a local variable, you can use the DECLARE statement or use it as a parameter within a STORED PROCEDURE statement.

When declaring a local variable, you can optionally assign a default value to it. If you assign no default value, the variable is initialized with a NULL value.

Each variable lives within a scope, delimited by the BEGIN ... END block containing its declaration.

For Example declare :

DECLARE variable TYPE [DEFAULF VALUE]

DECLARE a INT; 
DECLARE a INT DEFAULT 0;

DECLARE a VARCHAR(12);
DECLARE a VARCHAR(12) DEFAULT '';

DECLARE a CHAR;
DECLARE a CHAR DEFAULT '';

DECLARE a FLOAT;
DECLARE a FLOAT DEFAULT 0.0;

DECLARE a BOOLEAN;
DECLARE a BOOLEAN DEFAULT TRUE;

Other Example :

DELIMITER $$

CREATE PROCEDURE GetUpdatedPrices(itemcount INT)
BEGIN
    DECLARE factor DECIMAL(5, 2);
    SET factor:=3.45;
    SELECT PartNo, Description, itemcount * factor * ListPrice FROM Catalogue;
END
$$

DELIMITER ;

You must define the type and optionally the value it contains, for example if it is a counter it is logical that the value of the default variable is 0.

And the rest as you have it.

Javier G.Raya
  • 230
  • 1
  • 3
  • 15