Trying to run a procedure, error says result consist more than one line There is an officecode and employeeNumber in the employees table, which references city in office table.

I want to create a stored procedure setRelocationFee to set the relocation fee for a given employee. If the employee’s office is in San Francisco, the relocation fee is $10000; if the employee’s office is in Boston, the relocation fee is $8000; if the employee’s office is in London, the relocation fee is $20000; if the employee works in other offices, the relocation fee is $15000.

I used these codes to test the procedure, and it gives me error saying result consist more than one line:

set @empID = 1501;  call setRelocationFee(@empID, @Rfee);   select @empID, @Rfee;   

DELIMITER |

CREATE PROCEDURE setRelocationFee(IN empID INT(20), OUT Rfee INT(20)) BEGIN
    DECLARE relocation varchar(50) default 0;
    SELECT city INTO relocation 
    FROM employees LEFT JOIN offices ON 
    employeeNumber = empID;

    CASE relocation
    WHEN 'San Francisco' THEN SET Rfee = '$10000';
    WHEN 'Boston' THEN SET Rfee = '$8000';
    WHEN 'London' THEN SET Rfee = '$20000';

    ELSE
    SET Rfee = '$15000';
    END CASE;  END |
I don't understand why does you want to implement this in the SP form, but if, then (a variant with minimal edition)

DELIMITER |;

CREATE PROCEDURE setRelocationFee(IN empID INT(20), OUT Rfee INT(20)) 
BEGIN
    DECLARE relocation varchar(50) default 0;
    SELECT city INTO relocation 
    FROM employees 
    LEFT JOIN offices ON employeeNumber = empID;

    SET Rfee = CASE relocation
               WHEN 'San Francisco' THEN '$10000'
               WHEN 'Boston'        THEN '$8000'
               WHEN 'London'        THEN '$20000'
               ELSE                      '$15000'
               END ;  
END |;

DELIMITER ;