Stored Procedures & Functions

#Introduction

Stored procedures and stored functions are widely used in banking, ERP, payroll, insurance, e-commerce and enterprise reporting systems.

This chapter covers what stored procedures are and why they're used, creating and calling them, IN / OUT / INOUT parameters, variables, IF and CASE, WHILE / REPEAT / LOOP, error handling, user-defined (stored) functions, enterprise use cases and interview questions.

#What Is a Stored Procedure?

A stored procedure is a collection of SQL statements stored inside the database that can be executed whenever required — think of it as a method in Java.

public void calculateSalary() { ... }

CREATE PROCEDURE calculate_salary()
BEGIN
    ...
END;

#Why Use Stored Procedures?

Without a procedure, every application sends its own SQL — the same statements may be sent from many applications. With one:

Benefits:

• reusable database logic;
• fewer network round trips;
• centralized data operations;
• better security through controlled access (grant EXECUTE without granting table access);
• easier reuse across applications.

#A Basic Procedure

DELIMITER $

CREATE PROCEDURE get_employees()
BEGIN
    SELECT * FROM employee;
END$

DELIMITER ;

CALL get_employees();

#Parameters

IN Parameter

IN is the default parameter type:

DELIMITER $

CREATE PROCEDURE get_employee_by_id(IN emp_id INT)
BEGIN
    SELECT * FROM employee WHERE employee_id = emp_id;
END$

DELIMITER ;

CALL get_employee_by_id(101);

Multiple Parameters

DELIMITER $

CREATE PROCEDURE get_employees_by_department(
    IN dept       VARCHAR(50),
    IN min_salary DECIMAL(10, 2)
)
BEGIN
    SELECT *
    FROM employee
    WHERE department = dept
      AND salary >= min_salary;
END$

DELIMITER ;

CALL get_employees_by_department('IT', 70000);

OUT Parameter

An OUT parameter returns a value to the caller:

DELIMITER $

CREATE PROCEDURE get_employee_count(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM employee;
END$

DELIMITER ;

CALL get_employee_count(@count);
SELECT @count;

INOUT Parameter

An INOUT parameter is both input and output:

DELIMITER $

CREATE PROCEDURE increase_value(INOUT number INT)
BEGIN
    SET number = number + 10;
END$

DELIMITER ;

SET @num = 20;
CALL increase_value(@num);
SELECT @num;

30

IN vs OUT vs INOUT

#Local Variables

Variables inside stored programs are declared with DECLARE:

DELIMITER $

CREATE PROCEDURE demo_variable()
BEGIN
    DECLARE total_salary DECIMAL(12, 2);

    SELECT SUM(salary) INTO total_salary FROM employee;

    SELECT total_salary;
END$

DELIMITER ;

Local variables (total_salary) exist only inside the block. User variables (@count) belong to the session and need no declaration.

#IF Statement

DELIMITER $

CREATE PROCEDURE calculate_bonus(
    IN  emp_salary DECIMAL(10, 2),
    OUT bonus      DECIMAL(10, 2)
)
BEGIN
    IF emp_salary > 50000 THEN
        SET bonus = 5000;
    ELSE
        SET bonus = 2000;
    END IF;
END$

DELIMITER ;

ELSEIF adds further branches.

#CASE Statement

Useful when several conditions need evaluating:

CASE department
    WHEN 'IT' THEN SELECT 'Technology';
    WHEN 'HR' THEN SELECT 'Human Resources';
    ELSE SELECT 'Other';
END CASE;

#Loops

WHILE

Checks the condition before executing:

DELIMITER $

CREATE PROCEDURE print_numbers()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 5 DO
        SELECT i;
        SET i = i + 1;
    END WHILE;
END$

DELIMITER ;

Each SELECT i inside a loop produces a separate result set — fine for a demo, but real loops usually build a value or insert rows.

REPEAT

Executes at least once, because the condition is checked after execution:

DELIMITER $

CREATE PROCEDURE repeat_example()
BEGIN
    DECLARE i INT DEFAULT 1;

    REPEAT
        SELECT i;
        SET i = i + 1;
    UNTIL i > 5
    END REPEAT;
END$

DELIMITER ;

LOOP

LOOP is an unconditional loop, usually combined with a label and LEAVE (and ITERATE to skip to the next iteration):

DELIMITER $

CREATE PROCEDURE loop_example()
BEGIN
    DECLARE i INT DEFAULT 1;

    number_loop: LOOP
        IF i > 5 THEN
            LEAVE number_loop;
        END IF;

        SELECT i;
        SET i = i + 1;
    END LOOP number_loop;
END$

DELIMITER ;

#Exception Handling

Stored programs use handlers to manage SQL errors:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
END;

Common handler conditions are SQLEXCEPTION, SQLWARNING, NOT FOUND (used with cursors and SELECT ... INTO that finds no row) and specific error codes or SQLSTATE values.

You can raise your own errors with SIGNAL:

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';

#Transactions Inside Procedures

Transactions are essential when multiple operations must succeed or fail together — a bank transfer:

START TRANSACTION;

UPDATE account SET balance = balance - 1000 WHERE account_id = 1;
UPDATE account SET balance = balance + 1000 WHERE account_id = 2;

COMMIT;
-- on error: ROLLBACK;

Bank Transfer Procedure

DELIMITER $

CREATE PROCEDURE transfer_money(
    IN from_account    INT,
    IN to_account      INT,
    IN transfer_amount DECIMAL(12, 2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE account
    SET balance = balance - transfer_amount
    WHERE account_id = from_account;

    UPDATE account
    SET balance = balance + transfer_amount
    WHERE account_id = to_account;

    COMMIT;
END$

DELIMITER ;

CALL transfer_money(1, 2, 1000);

A real banking system needs more validation: sufficient balance, valid accounts, concurrent transactions, isolation and audit logging. A more defensive version locks the source row, checks the balance and verifies both updates hit a row:

DELIMITER $

CREATE PROCEDURE transfer_money_safe(
    IN from_account    INT,
    IN to_account      INT,
    IN transfer_amount DECIMAL(12, 2)
)
BEGIN
    DECLARE current_balance DECIMAL(12, 2);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    IF transfer_amount <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Amount must be positive';
    END IF;

    START TRANSACTION;

    -- Lock the source row so concurrent transfers can't overdraw it
    SELECT balance INTO current_balance
    FROM account
    WHERE account_id = from_account
    FOR UPDATE;

    IF current_balance IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Source account not found';
    END IF;

    IF current_balance < transfer_amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
    END IF;

    UPDATE account SET balance = balance - transfer_amount WHERE account_id = from_account;
    UPDATE account SET balance = balance + transfer_amount WHERE account_id = to_account;

    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Destination account not found';
    END IF;

    COMMIT;
END$

DELIMITER ;

SIGNAL raises an SQLEXCEPTION, so the handler rolls back and RESIGNALs the message to the caller. (In production, lock both accounts in a consistent order — e.g. lowest ID first — to avoid deadlocks between opposite transfers.)

#Stored Functions (User-Defined Functions)

A stored function is a reusable database function that returns exactly one value. Unlike procedures, functions can be used inside SQL expressions; procedures are invoked with CALL.

DELIMITER $

CREATE FUNCTION annual_salary(monthly_salary DECIMAL(10, 2))
RETURNS DECIMAL(12, 2)
DETERMINISTIC
BEGIN
    RETURN monthly_salary * 12;
END$

DELIMITER ;

SELECT employee_name, annual_salary(salary)
FROM employee;

DETERMINISTIC vs NOT DETERMINISTIC

• DETERMINISTIC — the same input always gives the same output: annual_salary(50000) is 600000 every time.
• NOT DETERMINISTIC (the default) — the same input may give different results: functions using the current date/time, random values or changing table data.

Procedure vs Function

#Real-World Examples

-- 1. Employee search
CALL get_employee_by_id(101);

-- 2. Payroll calculation
SELECT employee_name, annual_salary(salary) FROM employee;

-- 3. Bank fund transfer: START TRANSACTION → debit A → credit B → COMMIT (ROLLBACK on failure)
CALL transfer_money(1, 2, 1000);

-- 4. Monthly report generation
CALL generate_monthly_report();

-- 5. Inventory update
CALL update_inventory(101, 5);

#Performance and Design Considerations

Stored procedures can:

• reduce network round trips;
• execute data-centric logic close to the data;
• improve code reuse;
• simplify repeated multi-step database operations.

However:

• complex business logic is often easier to maintain in the application layer;
• database code is harder to unit test;
• versioning and deployment need proper migration processes;
• heavy use creates database vendor lock-in.

A common enterprise split: data-centric operations → stored procedures; complex business workflows → Java / the application layer.

#Stored Procedures in Spring Boot

With plain JDBC, use a CallableStatement:

try (CallableStatement statement = connection.prepareCall("{call get_employee_by_id(?)}")) {
    statement.setInt(1, 101);
    try (ResultSet rs = statement.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString("employee_name"));
        }
    }
}

With Spring Data JPA, @Procedure maps naturally to procedures with OUT parameters:

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

    @Procedure(procedureName = "get_employee_count")
    Integer getEmployeeCount();          // returns the OUT parameter

    @Query(value = "CALL get_employee_by_id(:id)", nativeQuery = true)
    List<Employee> findByIdViaProcedure(@Param("id") Integer id);   // result set
}

For procedures that return a result set, a native @Query with CALL (or @NamedStoredProcedureQuery with resultClasses) is the reliable option on MySQL. Spring's SimpleJdbcCall is another clean choice.

Stored procedures are useful when legacy systems already use them, when complex database operations must run atomically, when several applications share the same database logic, or when database-side processing gives a measurable benefit.

#Mini Project

Using an employee table (employee_id, name, department, salary):

1. Create a procedure to retrieve an employee by ID.
2. Create a procedure to count employees in a department.
3. Create a procedure that increases salary by a given percentage.
4. Create a function to calculate annual salary.
5. Create a procedure to transfer money between two bank accounts using a transaction.

#Practice Problems

Easy

1. Create a basic procedure.
2. Create a procedure with an IN parameter.
3. Create a procedure with an OUT parameter.
4. Create a simple function.
5. Call a stored procedure.

Medium

1. Use IF inside a procedure.
2. Use CASE inside a procedure.
3. Create a WHILE loop.
4. Create a procedure using transactions.
5. Build an inventory update procedure.

Interview level

1. Explain procedure vs function.
2. Explain IN, OUT and INOUT.
3. Explain exception handling in stored procedures.
4. Design a bank transfer procedure using transactions.
5. Discuss when business logic should stay in Java vs move into stored procedures.

#Key Takeaways

• Stored procedures encapsulate reusable SQL logic inside the database.
• IN, OUT and INOUT parameters control how data flows in and out.
• Local variables are declared with DECLARE.
• IF, CASE, WHILE, REPEAT and LOOP provide procedural control flow.
• Handlers manage SQL errors; transactions make multi-step operations succeed or fail together.
• Functions return a single value and can be used directly in SQL statements.
• Use stored procedures where database-side execution adds value, but don't push all application logic into the database.