MySql

Stored Procedures and Functions

️ Stored Procedures and Functions: Automating Logic Inside Your Database

Modern applications rely heavily on databases—but handling repetitive tasks or complex logic in raw SQL can quickly become messy. That’s where stored procedures and functions come in.

These two features allow you to encapsulate logic directly inside your database, improving performance, maintainability, and reusability.

In this blog, we’ll explore:

  • What stored procedures and functions are

  • Their differences

  • When to use them

  • Real examples in SQL

  • Best practices

What Are Stored Procedures?

A stored procedure is a precompiled set of SQL statements that can perform operations like inserts, updates, loops, and conditional logic.

Stored procedures are ideal for:

  • Running business logic at the database level

  • Performing multi-step operations

  • Reducing traffic between your application and the database

✅ Example: Stored Procedure in MySQL

sql
DELIMITER //

CREATE PROCEDURE UpdateEmployeeSalary (
IN emp_id INT,
IN increase_percent DECIMAL(5,2)
)
BEGIN
UPDATE employees
SET salary = salary + (salary * increase_percent / 100)
WHERE id = emp_id;
END //

DELIMITER ;

To call it:

sql
CALL UpdateEmployeeSalary(101, 10);

This increases an employee’s salary by 10%.

What Are Functions?

A function is similar to a stored procedure, but with one key difference: it returns a value and is generally used in queries.

Functions are ideal for:

  • Calculations

  • Transformations

  • Validations

  • Reusing logic in SELECT statements

✅ Example: SQL Function

sql
CREATE FUNCTION CalculateTax(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN amount * 0.07; -- 7% tax
END;

To use the function:

sql
SELECT order_id, amount, CalculateTax(amount) AS tax
FROM orders;

Key Differences: Stored Procedure vs Function

Feature Stored Procedure Function
Returns value Optional Mandatory (must return a value)
Use in SELECT ❌ Not allowed ✅ Allowed
Use in SQL statements Called via CALL Used in queries like expressions
Output parameters Supported Not supported
Use cases Data manipulation, logic Calculations, data retrieval

When to Use Each

Use Case Recommended
Need to modify data (e.g., insert/update/delete) Stored Procedure
Need to encapsulate logic that returns a single value Function
Want reusable business logic across apps Both
Use in SELECT queries Function

Benefits of Using Stored Procedures and Functions

  • ✅ Encapsulation of logic: Reduces repeated code

  • ✅ Improved performance: Precompiled execution in some systems

  • ✅ Reduced network traffic: Less back-and-forth between app and DB

  • ✅ Better security: Control access with GRANT/REVOKE

  • ✅ Maintainability: Change logic in one place without altering application code

Best Practices

  1. Use meaningful names for procedures and functions.

  2. Keep them small and focused—each should do one thing well.

  3. Avoid complex logic if possible—consider whether logic belongs in the app layer.

  4. Document input/output parameters for clarity.

  5. Use error handling (e.g., DECLARE ... HANDLER) to manage exceptions.

  6. Benchmark performance—functions inside large SELECTs can slow things down if misused.

Final Thoughts

Stored procedures and functions are powerful tools that bring structure, reusability, and performance benefits to your SQL code. They allow you to treat your database not just as a storage engine—but as an active part of your application’s logic.

Use stored procedures for complex, multi-step operations and data updates. Use functions for reusable, returnable logic inside your queries.

Want to learn how to debug or optimize stored procedures? Or see examples in PostgreSQL or SQL Server? Let us know in the comments below.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *