CODEWITHSUNDEEP
mysqldatabasesetlocal-variablesdeclare
CodeKingPlusPlus
CodeKingPlusPlus

Reputation: 16081

MySQL local variables

I am trying to define and initialize a MySQL variable for a query.

I have the following:

DECLARE @countTotal INT;
SET @countTotal = SELECT COUNT(*) FROM nGrams;

I am using MySQL in Netbeans and it tells me I have an error. What/where is my error?

How can I fix this?

Upvotes: 32

Views: 51077

Answers (5)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

You can set local variables with DECLARE statement as shown below:

DELIMITER $$

CREATE FUNCTION my_func() RETURNS INT 
DETERMINISTIC
BEGIN
  DECLARE v1, v2 INT DEFAULT 2;       -- v1 and v2 are 2.
  DECLARE v3, v4 INT DEFAULT v1 + v2; -- v3 and v4 are 4.
  DECLARE result INT;                 -- result is NULL.
  SELECT v3 + v4 INTO result;         -- result is 8. 
  RETURN result;                      -- 8 is returned.
END$$ 

DELIMITER ;

*Memos:

Then, 8 is returned as shown below:

mysql> SELECT my_func();
+-----------+
| my_func() |
+-----------+
|         8 |
+-----------+

Be careful, if DECLARE block is not set within the top of BEGIN ... END statement within stored programs as shown below:

DELIMITER $$

CREATE FUNCTION my_func() RETURNS INT 
DETERMINISTIC
BEGIN
  SELECT 2 + 3;                       -- Because of this
  DECLARE v1, v2 INT DEFAULT 2;       -- Not the top
  DECLARE v3, v4 INT DEFAULT v1 + v2; -- Not the top
  DECLARE result INT;                 -- Not the top
  SELECT v3 + v4 INTO result;
  RETURN result;
END$$ 

DELIMITER ;

Or:

DELIMITER $$

CREATE FUNCTION my_func() RETURNS INT 
DETERMINISTIC
BEGIN
  DECLARE v1, v2 INT DEFAULT 2;
  DECLARE v3, v4 INT DEFAULT v1 + v2;
  SELECT 2 + 3;       -- Because of this
  DECLARE result INT; -- Not the top
  SELECT v3 + v4 INTO result;
  RETURN result;
END$$ 

DELIMITER ;

Then, there is the error below:

ERROR 1064 (42000): 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 ...

Upvotes: -1

eggyal
eggyal

Reputation: 125985

MySQL has two different types of variable:

  • local variables (which are not prefixed by @) are strongly typed and scoped to the stored program block in which they are declared. Note that, as documented under DECLARE Syntax:

    DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

  • user variables (which are prefixed by @) are loosely typed and scoped to the session. Note that they neither need nor can be declared—just use them directly.

Therefore, if you are defining a stored program and actually do want a "local variable", per the wording in your question, you will need to drop the @ character and ensure that your DECLARE statement is at the start of your program block. Otherwise, to use a "user variable", drop the DECLARE statement.

Furthermore, you will either need to surround your query in parentheses in order to execute it as a subquery:

SET @countTotal = (SELECT COUNT(*) FROM nGrams);

Or else, you could use SELECT ... INTO:

SELECT COUNT(*) INTO @countTotal FROM nGrams;

Upvotes: 53

alditis
alditis

Reputation: 4817

Function example:

DROP FUNCTION IF EXISTS test;

DELIMITER $$
CREATE FUNCTION test(in_number INT) RETURNS INT
    BEGIN
        DECLARE countTotal INT;
        SET countTotal = SELECT COUNT(*) FROM nGrams;
    RETURN countTotal + in_number;
END $$
DELIMITER ;

Upvotes: 4

Rahul Tripathi
Rahul Tripathi

Reputation: 172578

Try this:-

 select @countTotal := COUNT(*) from nGrams;

Upvotes: 8

Olaf Dietsche
Olaf Dietsche

Reputation: 74088

According to DECLARE Syntax, declare must be inside a begin...end block.

Upvotes: 1

Related Questions