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!

MikeTheReader · Accepted Answer · 2010-08-31 21:33:59Z

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);

answered Aug 31, 2010 at 21:33

MikeTheReader

4,1903 gold badges27 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem Setting Variable value in MySQL Procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related