What is a stored procedure and how does it differ from a function?

Stored Procedure:

• A set of SQL commands stored on the server for reuse.
• Can perform SELECT, INSERT, UPDATE, DELETE operations.
• May return multiple result sets or return nothing.
• Can have input and output parameters.
• Cannot be called within an SQL query.

Example of a stored procedure:

MySQL 8.1
CREATE PROCEDURE GetEmployeeByID(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE id = emp_id;
END;

Calling the procedure:

MySQL 8.1
CALL GetEmployeeByID(1);

Function:

• Returns a single value (scalar function) or a table (table-valued function).
• Can be used in SQL expressions (e.g., in SELECT or WHERE clauses).
• Must return a value.
• Typically used for calculations and returns a deterministic result.

Example of a function:

MySQL 8.1
CREATE FUNCTION GetEmployeeSalary(emp_id INT) RETURNS DECIMAL(10,2)
BEGIN
    DECLARE salary DECIMAL(10,2);
    SELECT e.salary INTO salary FROM employees e WHERE e.id = emp_id;
    RETURN salary;
END;

Using the function:

MySQL 8.1
SELECT name, GetEmployeeSalary(id) FROM employees;