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?
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 aBEGIN ... 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;