Database basic tutorial - part 14: Stored Procedure

SQL Script

SET SQL_SAFE_UPDATES = 0;

USE JadeDb;


DROP TABLE IF EXISTS Employees;


CREATE TABLE IF NOT EXISTS Employees (id INT PRIMARY KEY AUTO_INCREMENT,
                                                         `name` VARCHAR(30),
                                                                department_id INT, salary FLOAT);


DROP TABLE IF EXISTS Departments;


CREATE TABLE IF NOT EXISTS Departments (department_id INT PRIMARY KEY AUTO_INCREMENT,
                                                                      department_name VARCHAR(20),
                                                                                      LOCATION VARCHAR(20));


INSERT INTO Departments (department_name, LOCATION)
VALUES ('HR', 'New York'),
       ('Engineering', 'San Francisco'),
       ('Sales', 'Chicago');


INSERT INTO Employees (`name`, department_id, salary)
VALUES ('Alice', 1, 70000),
       ('Bob', 2, 80000),
       ('Charlie', 3, 60000),
       ('David', 3, 75000),
       ('Eve', 2, 90000);


DROP PROCEDURE IF EXISTS get_departments_name;


DELIMITER $$

CREATE PROCEDURE get_departments_name(IN in_department_id INT, OUT out_department_name VARCHAR(40))
	BEGIN
		SELECT department_name INTO out_department_name
        FROM departments
        WHERE department_id = in_department_id;
    END$$
DELIMITER ;


SET @department_name = '';

CALL get_departments_name(1, @department_name);


SELECT @department_name;