✏️ Explanatory Question
Level: Advanced — A very common question; the key differences lie in return values and where they can be used.
Both are stored programs containing reusable SQL logic, but they differ in how they return values and where they can be called.
CALL. Can perform DML and manage transactions.SELECT, WHERE). Must have a RETURNS clause.SELECT fn(x)), while a procedure is called separately with CALL and is used for performing actions, not for use inside expressions.
| Feature | Stored Procedure | Function |
|---|---|---|
| Return value | Zero, one, or many (OUT) | Exactly one (RETURNS) |
| Called with | CALL | Inside SQL expressions |
| Use in SELECT/WHERE? | No | Yes |
| Parameters | IN, OUT, INOUT | IN only |
| DML (INSERT/UPDATE) | Allowed | Restricted |
| Transactions | Can manage | Cannot manage |
| Try/Catch (handlers) | Allowed | Allowed (limited) |
-- FUNCTION: returns a single value, usable in a query
DELIMITER $$
CREATE FUNCTION GetAnnualSalary(p_monthly DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN p_monthly * 12;
END $$
DELIMITER ;
-- Use it directly inside a SELECT
SELECT name, GetAnnualSalary(salary) AS yearly FROM employees;
-- PROCEDURE: performs an action, called with CALL
DELIMITER $$
CREATE PROCEDURE GiveRaise(IN p_id INT, IN p_amount DECIMAL(10,2))
BEGIN
UPDATE employees SET salary = salary + p_amount WHERE emp_id = p_id;
END $$
DELIMITER ;
CALL GiveRaise(1, 5000);DETERMINISTIC if it always returns the same output for the same input — this helps the optimizer and is often required depending on binary logging settings.