SQL Median Value Undeclared Variable

Question

I am trying to create a stored procedure to obtain the median age within a table but am getting an undeclared variable error. My code is:

DELIMITER //
CREATE PROCEDURE MedianAge()
BEGIN

SET @row_count = (SELECT COUNT(*) FROM employee);
SET @median_index = (@row_count/2);

SELECT  TIMESTAMPDIFF(YEAR, bdate, CURDATE()) AS age 
FROM employee ORDER BY bdate DESC 
LIMIT median_index, median_index;


END //
DELIMITER ;

I am receiving the error:

Error Code: 1327. Undeclared variable: median_index

As far as I am aware I have declared the variable correctly and am unsure why the SELECT statement does not work.

Ildar Akhmetov · Accepted Answer · 2018-08-10T14:24:36.567

3

You are using user-defined variables (prefixed with @). However, within the stored procedures you should use local variables (with no prefix). These variables have to be declared prior to use. The difference is well explained here.

You would get something like this:

DELIMITER //
CREATE PROCEDURE MedianAge()
BEGIN

DECLARE row_cnt INT unsigned;
DECLARE median_index INT unsigned;

SET row_cnt = (SELECT COUNT(*) FROM employees);
SET median_index = (row_cnt/2);

SELECT  TIMESTAMPDIFF(YEAR, bdate, CURDATE()) AS age 
FROM employee ORDER BY bdate DESC 
LIMIT median_index, median_index;


END //
DELIMITER ;

Also, keep in mind that row_count is a reserved word in MySQL (a function name). It's better to avoid using it as a variable name.

edited Aug 10 '18 at 14:24

answered Aug 10 '18 at 14:19

Ildar Akhmetov

1,331
13
22

Thanks for clarifying the difference, that is understood now. I am also getting error code 1064 trying to implement `DECLARE row_cnt INT unsigned;` – JackDG Aug 10 '18 at 14:27
The code above worked just fine for me. Are you sure that the error arises for the row_cnt declaration? – Ildar Akhmetov Aug 10 '18 at 14:30
I'm not sure what the issue was, I attempted again and it's working perfectly. Thanks very much for your assistance. I will mark this as correct. – JackDG Aug 10 '18 at 14:34
Glad it helped! – Ildar Akhmetov Aug 10 '18 at 14:36

SQL Median Value Undeclared Variable

1 Answers1