✏️ Explanatory Question
Level: Advanced — Stored procedures bundle reusable business logic directly inside the database.
A stored procedure is a precompiled set of SQL statements stored in the database under a name, which can be executed (called) whenever needed. It can accept parameters, contain control-flow logic (IF, loops), and perform multiple operations in one call.
| Mode | Direction | Purpose |
|---|---|---|
| IN | Input (default) | Pass a value into the procedure |
| OUT | Output | Return a value to the caller |
| INOUT | Both | Pass in and return modified value |
$$) so MySQL doesn't treat the inner semicolons as the end of the whole procedure. Reset it back to ; afterward.
-- Change delimiter so inner ; are not treated as end of procedure
DELIMITER $$
CREATE PROCEDURE GetEmployeesByDept(IN p_dept_id INT)
BEGIN
SELECT emp_id, name, salary
FROM employees
WHERE dept_id = p_dept_id;
END $$
DELIMITER ;
-- Call the procedure
CALL GetEmployeesByDept(10);
-- Procedure with an OUT parameter
DELIMITER $$
CREATE PROCEDURE CountEmployees(IN p_dept INT, OUT p_total INT)
BEGIN
SELECT COUNT(*) INTO p_total
FROM employees WHERE dept_id = p_dept;
END $$
DELIMITER ;
CALL CountEmployees(10, @total);
SELECT @total; -- retrieve the returned value