-- Drop the Table if it exists
DROP TABLE IF EXISTS tbl_employees;
CREATE TABLE tbl_employees (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(50),
emp_departmentid INT,
emp_salary DECIMAL(10, 2)
);
INSERT INTO tbl_employees(emp_name, emp_departmentid, emp_salary)
VALUES
('Nicole', 2, 1421.42),
('Michael', 3, 4262.53),
('Vince', 1, 6314.96),
('Kobe', 2, 8361.21),
('Bryan', 3, 3626.64),
('Loki', 1, 1332.12),
('Tomo', 3, 12242.88),
('Vilma', 3, 6327.24),
('Petra', 1, 4829.84);
-- Drop the Function if it exists
DROP FUNCTION IF EXISTS fn_getEmpNameByEmpDeptID;
-- Create the Function with Two Parameters
DELIMITER $$
CREATE FUNCTION fn_getEmpNameByEmpDeptID(_id INT, _department_id INT)
RETURNS VARCHAR(50)
BEGIN
DECLARE _EmpName VARCHAR(50);
SELECT emp_name INTO _EmpName FROM tbl_employees
WHERE _id = emp_id AND _department_id = emp_departmentid
LIMIT 1;
RETURN _EmpName;
END $$
DELIMITER ;
-- EXECUTE
SELECT fn_getEmpNameByEmpDeptID(1, 2);
SELECT fn_getEmpNameByEmpDeptID(1, 3);
SELECT fn_getEmpNameByEmpDeptID(8, 3);
SELECT * FROM tbl_employees
ORDER BY emp_departmentid;
Telegram: DB developers channel