CODEWITHSUNDEEP
mysqlsql
user1076802
user1076802

Reputation: 317

SELECT INTO local variable undeclared?

I have the following in an sql file I am executing...

DECLARE rowcount INT;

SELECT COUNT(*) INTO rowcount
FROM VRG_PROBLEM_ACCOUNT PA 
WHERE NEW.CustName = PA.CustNAME 
AND NEW.AreaCode = PA.AreaCode 
AND NEW.PhoneNumber = PA.PhoneNumber;

And yet I'm getting

ERROR 1327 (42000): Undeclared variable: rowcount

In another file I am doing the same type of SELECT...INTO localvariable and it works.

Upvotes: 2

Views: 6827

Answers (2)

GregD
GregD

Reputation: 2877

How about re-writing your query a bit?

DECLARE rowcount INT;

SET rowcount = (
    SELECT COUNT(*)
      FROM VRG_PROBLEM_ACCOUNT PA 
     WHERE PA.CustNAME = NEW.CustName
       AND PA.AreaCode = NEW.AreaCode
       AND PA.PhoneNumber = NEW.PhoneNumber);

UPDATE 1*

I do not know your full code, but please keep in mind that DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

Upvotes: 0

siride
siride

Reputation: 210025

Local variables can only be declared inside of stored routines. You can use @-variables instead, which don't need to be declared at all:

SELECT COUNT(*) INTO @rowcount
FROM ...

Upvotes: 4

Related Questions