Stored Procedures

What Is a Stored Procedure?

A stored procedure is a collection of SQL statements stored in the database that can be called repeatedly. It accepts parameters and returns results.

Creating a Stored Procedure

CREATE PROCEDURE sp_view_employees()
BEGIN
    SELECT employee_id, name, department, salary
    FROM EMPLOYEE
    WHERE status = 'Active';
END;

Calling a Stored Procedure

CALL sp_view_employees();

IN Parameters

IN parameters receive values from the caller.

CREATE PROCEDURE sp_find_employee(IN p_department VARCHAR(50))
BEGIN
    SELECT * FROM EMPLOYEE
    WHERE department = p_department;
END;

CALL sp_find_employee('Technology');

OUT Parameters

OUT parameters return values to the caller.

CREATE PROCEDURE sp_count_employees(OUT p_amount INT)
BEGIN
    SELECT COUNT(*) INTO p_amount
    FROM EMPLOYEE
    WHERE status = 'Active';
END;

INOUT Parameters

CREATE PROCEDURE sp_naik_salary(INOUT p_persen DECIMAL(5,2))
BEGIN
    UPDATE EMPLOYEE
    SET salary = salary * (1 + p_persen / 100);
END;

Example: Adding an Employee

CREATE PROCEDURE sp_add_employee(
    IN p_name VARCHAR(100),
    IN p_dept VARCHAR(50),
    IN p_salary DECIMAL(10,2)
)
BEGIN
    INSERT INTO EMPLOYEE (name, department, salary, status, hire_date)
    VALUES (p_name, p_dept, p_salary, 'Active', CURDATE());
END;

CALL sp_add_employee('John', 'Marketing', 7500000);

Dropping a Stored Procedure

DROP PROCEDURE sp_view_employees;

DROP PROCEDURE IF EXISTS sp_view_employees;

Advantages of Stored Procedures

Tips

• Use descriptive names (with the sp_ prefix)
• Use parameters for flexibility
• Handle errors with DECLARE ... HANDLER

Stored procedures make automation easier. The final chapter presents the SQL Cheatsheet as a quick reference.