What is the difference between a stored procedure and a function in SQL?
A function must return a value and can be used inside queries, usually without side effects. A procedure is called with CALL or EXEC, can return nothing or several outputs, and can change data and manage transactions.
Explain it simply
A function is a calculator button: you put a value in, you get a value out, and you can use that button in the middle of a formula. A procedure is a full task, like "process the monthly salary run", that you start on its own and that may do many things.
Example
-- function, usable inside SELECT (MySQL syntax)
CREATE FUNCTION gst(amount DECIMAL(10,2)) RETURNS DECIMAL(10,2)
DETERMINISTIC
RETURN amount * 0.18;
SELECT price, gst(price) FROM products;
-- procedure, called separately
CREATE PROCEDURE pay_salary(IN month_no INT)
BEGIN
INSERT INTO payslips SELECT id, salary, month_no FROM employees;
UPDATE employees SET last_paid = month_no;
END;
CALL pay_salary(10);| Function | Procedure | |
|---|---|---|
| Return value | Required | Optional, OUT params |
| Used in SELECT | Yes | No |
| Change data | Restricted in many databases | Yes |
| Transactions | Generally no | Yes, in most databases |
Details differ by database. For instance, PostgreSQL functions can modify data, and PostgreSQL only added procedures with transaction control in version 11.
What to say in the interview
"Functions return a value and plug into queries. Procedures perform operations, can return multiple outputs, and can handle transactions. The rules vary slightly between databases."
Common mistakes
- Putting heavy data-modifying logic into functions called per row in a query.