Problem Setting Variable value in MySQL Procedure

Question

Having trouble setting the value of a declared variable in MySQL. If I run the following in MySQL Command Line it all works great:

SET @numberOfMonths = (SELECT COUNT(\*) FROM (SELECT DISTINCT months WHERE year = 2010) as A);  
SELECT @numberOfMonths;  

So it returns 6 for this particular example.
If I do the following I don't have any problems either:

DELIMITER @@  
CREATE PROCEDURE GetPropertyTenantPayment()    
BEGIN  
DECLARE done INT DEFAULT 0;   
DECLARE numberOfMonths INT DEFAULT 6;  
....  
END;  
@@  

No, problem, the procedure works as it should but once I do this:

DELIMITER @@  
CREATE PROCEDURE GetPropertyTenantPayment()  
BEGIN    
DECLARE done INT DEFAULT 0;  
DECLARE numberOfMonths INT;  
SET numberOfMonths = (SELECT COUNT(\*) FROM (SELECT DISTINCT month WHERE year = 2010) as A);    
...  
END;  
@@

I get a syntax error at the SET numberOfMonths line. I don't really understand why? I do the same outside of the procedure and there is no problem. Thanks for the help!

Answer 1

Don't know if it's the answer you're looking for, but whenever I'm working inside a stored procedure, I use the SELECT ... INTO method for setting variables. So, in this case, you could use:

SELECT COUNT(*) INTO numberOfMonths FROM (SELECT DISTINCT month WHERE year = 2010);