MySQL - Stored Procedures and Functions in MySQL

1. Stored Procedures

What is a Stored Procedure?

A Stored Procedure is a set of SQL statements stored in the database that can be executed repeatedly.

• Helps in reusing code, simplifying complex operations, and improving performance.

• Can accept parameters (input, output, or both).

Syntax

CREATE PROCEDURE procedure_name (parameters)
BEGIN
    -- SQL statements
END;

Example

Suppose we have a table employees:

Create a procedure to give a raise to an employee:

DELIMITER $$

CREATE PROCEDURE give_raise(IN emp INT, IN increment DECIMAL(10,2))
BEGIN
    UPDATE employees
    SET salary = salary + increment
    WHERE emp_id = emp;
END$$

DELIMITER ;

Call the procedure:

CALL give_raise(2, 500);

• Bob’s salary will increase by 500.

2. Functions

What is a Function?

A Function is a stored program that returns a single value.

• Functions are often used in queries, calculations, and data transformations.

• Must return a value using RETURN.

Syntax

CREATE FUNCTION function_name (parameters)
RETURNS datatype
BEGIN
    -- SQL statements
    RETURN value;
END;

Example

Create a function to calculate yearly salary:

DELIMITER $$

CREATE FUNCTION yearly_salary(emp INT)
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE annual DECIMAL(10,2);
    SELECT salary * 12 INTO annual
    FROM employees
    WHERE emp_id = emp;
    RETURN annual;
END$$

DELIMITER ;

Use the function:

SELECT name, yearly_salary(emp_id) AS annual_salary
FROM employees;

3. Key Differences Between Stored Procedures and Functions

4. Best Practices

• Use procedures for actions (insert/update/delete) or multi-step tasks.

• Use functions for calculations or returning a value in queries.

• Always handle exceptions if possible using DECLARE ... HANDLER.

• Use meaningful names like sp_ prefix for procedures and fn_ prefix for functions.