What is the difference between a function and a stored procedure?

• Function:
• Return Type: Always returns a value (can be scalar or table).
• Used in Queries: Can be used in SELECT, WHERE, and ORDER BY clauses.
• Side Effects: Typically designed to perform calculations or return a result

without modifying the database.

• Stored Procedure:
• No Return Type: May or may not return values (using output parameters or

result sets).

• Used for Actions: Typically used for performing database operations (like

INSERT, UPDATE, DELETE).

• Side Effects: Designed to perform operations that modify data or perform

business logic.

Example:

Function (returns a value):

CREATE FUNCTION GetEmployeeSalary (emp_id INT) RETURNS DECIMAL

BEGIN

RETURN (SELECT salary FROM employees WHERE id = emp_id);

END;

Stored Procedure (performs an action):

CREATE PROCEDURE UpdateEmployeeSalary (IN emp_id INT, IN new_salary

DECIMAL)

BEGIN

UPDATE employees SET salary = new_salary WHERE id = emp_id;

END;