FUNCTION VS STORED PROCEDURE IN SPRING BOOT

When working with databases in Spring Boot, you’ll often come across two powerful constructs: functions and stored procedures. While they may look similar at first glance, they serve very different purposes.

Understanding this distinction helps you decide when to push logic into the database and how to integrate it properly into your application.

What is a Function?

A function is used to compute and return a value. It is typically used in read operations and can be directly invoked inside SQL queries.

Example: PostgreSQL Function

CREATE OR REPLACE FUNCTION get_employee_count_by_shift(shift_name TEXT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE total INT;
BEGIN
    SELECT COUNT(*) INTO total
    FROM employee e
    JOIN shift s ON e.shift_id = s.id
    WHERE s.name = shift_name;
RETURN total;
END;
$$;

This function calculates the number of employees assigned to a given shift and returns the result.

Calling Function from Spring Boot

@Query(value = "SELECT get_employee_count_by_shift(:shift)", nativeQuery = true)
Integer getCountByShift(@Param("shift") String shift);

Integer count = repository.getCountByShift("DAY_SHIFT");

Key Idea: Functions are ideal when you need a computed value from the database.

What is a Stored Procedure?

A stored procedure is designed to perform operations such as inserting, updating, or deleting data. It may or may not return a value, but its primary purpose is to execute a task.

Example: PostgreSQL Stored Procedure

CREATE OR REPLACE PROCEDURE assign_shift(emp_id INT, new_shift_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employee
    SET shift_id = new_shift_id
    WHERE id = emp_id;
END;
$$;

This procedure assigns a shift to an employee by updating the database.

Calling Procedure from Spring Boot

@Autowired
private EntityManager entityManager;
public void assignShift(Integer empId, Integer shiftId) {
    StoredProcedureQuery query =
        entityManager.createStoredProcedureQuery("assign_shift");
    query.registerStoredProcedureParameter("emp_id", Integer.class, ParameterMode.IN);
    query.registerStoredProcedureParameter("shift_id", Integer.class, ParameterMode.IN);
    query.setParameter("emp_id", empId);
    query.setParameter("shift_id", shiftId);
    query.execute();
}

Key Idea: Stored procedures are used for executing business logic directly inside the database.

Key Differences

Stored Function

A function is designed to return a value and behave like an expression inside SQL.

• Can be used inside SELECT, WHERE, etc.
• Must return a value (scalar, table, or VOID)
• Used for computations or data transformation
• Executed as part of a query

Example:

SELECT calculate_bonus(employee_id);

Think: “Give me a result”

Stored Procedure

A procedure is designed to perform actions or workflows rather than return values.

• Cannot be used inside SELECT
• Executed using CALL
• Can manage transactions (COMMIT, ROLLBACK)
• Used for bulk operations, business workflows, and multi-step logic

Example:

CALL update_salary_batch();

Think: “Perform this task”

Key Difference

• Function = returns result inside a query
• Procedure = executes a standalone operation

When to Use What?

• Use a function when you need a value (e.g., counts, calculations, aggregations).
• Use a stored procedure when performing operations like bulk updates, complex workflows, or transactional logic.

Final Thoughts

In most Spring Boot applications, standard JPA and queries are sufficient for everyday operations. However, functions and stored procedures become valuable when dealing with:

• Performance-critical database logic
• Complex data transformations
• Bulk operations

Used correctly, they help you keep your application efficient and your database logic well-organized.