CODEWITHSUNDEEP
mysqlstored-procedures
Orion447
Orion447

Reputation: 400

MySQL Undeclared variable error not sure why

In having trouble with this procedure that I wrote. Its giving me an undeclared var error and I don't know why. Ive tried

employee_id as id

with no luck.I also had both select MIN and MAX in the same statement but it was throwing the same undeclared error, so I had to split it. all suggestions are welcomed, thank you!

The Procedure

delimiter $
drop procedure if exists Q3;
create procedure Q3(emp1 decimal(6), emp2 decimal(6), emp3 decimal(6))
BEGIN
    declare sal1 decimal(8,2);  
    declare sal2 decimal(8,2);  
    declare sal3 decimal(8,2);  
    declare empH decimal(6);
    
    
    select MAX(sl) into sal1, employee_id into empH from (
        select salary as sl, employee_id 
        from employees 
        where employee_id = emp1 
        OR employee_id = emp2
        OR employee_id = emp3
    ) as emp_sl;
    
    select MIN(sl) into sal2 from (
        select salary as sl from employees 
        where employee_id = emp1 
        OR employee_id = emp2
        OR employee_id = emp3
    ) as sals;
    
    select sal1;
    select sal2;    
    
    
END$
delimiter ;

The employees table

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employee_id    | decimal(6,0) | NO   | PRI | NULL    |       |
| first_name     | varchar(20)  | YES  |     | NULL    |       |
| last_name      | varchar(25)  | NO   |     | NULL    |       |
| email          | varchar(25)  | NO   | UNI | NULL    |       |
| phone_INT      | varchar(20)  | YES  |     | NULL    |       |
| hire_date      | date         | NO   |     | NULL    |       |
| job_id         | varchar(10)  | NO   |     | NULL    |       |
| salary         | decimal(8,2) | YES  |     | NULL    |       |
| commission_pct | decimal(2,2) | YES  |     | NULL    |       |
| manager_id     | decimal(6,0) | YES  |     | NULL    |       |
| department_id  | decimal(4,0) | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

and the error

ERROR 1327 (42000) at line 3: Undeclared variable: employee_id

Upvotes: 0

Views: 728

Answers (1)

Robert
Robert

Reputation: 397

try changing

select MAX(sl) into sal1, employee_id into empH

to

select MAX(sl), employee_id into sal1, empH

everything after into needs to be a variable and only needs to be used once.

Upvotes: 1

Related Questions