Stored Procedures and Functions in SQL: Encapsulating Logic for Reuse and Automation

Table of Contents

1. Introduction
2. What Are Stored Procedures and Functions?
3. Benefits of Using Stored Routines
4. Stored Procedures vs Functions: Key Differences
5. Syntax for Creating Stored Procedures
6. Executing a Stored Procedure
7. Parameters in Stored Procedures (IN, OUT, INOUT)
8. Control Flow in Stored Procedures
9. Creating and Using Functions
10. Input Parameters in Functions
11. Return Statements and Data Types in Functions
12. Using Stored Functions in SQL Queries
13. Real-World Use Case: Generating Reports
14. Real-World Use Case: Reusable Business Rules
15. Error Handling in Stored Procedures
16. Security and Permissions
17. Performance Considerations
18. Modifying and Dropping Stored Routines
19. Best Practices for Writing Stored Routines
20. Summary and What’s Next

1. Introduction

In SQL, stored procedures and functions are blocks of SQL code that are stored in the database and executed when needed. They allow you to encapsulate complex operations into reusable and manageable routines.

2. What Are Stored Procedures and Functions?

• A Stored Procedure is a set of SQL statements that performs a task. It may or may not return a value.
• A Stored Function is similar but must return a value and is often used inside expressions.

Both are often referred to collectively as stored routines.

3. Benefits of Using Stored Routines

• Reusability: Write once, use many times
• Maintainability: Centralize business logic
• Security: Hide implementation details
• Performance: Reduce client-server communication
• Modularity: Break down large tasks into manageable pieces

4. Stored Procedures vs Functions: Key Differences

5. Syntax for Creating Stored Procedures

MySQL:

sqlCopyEditDELIMITER //

CREATE PROCEDURE GetEmployeeById(IN emp_id INT)
BEGIN
  SELECT * FROM employees WHERE id = emp_id;
END //

DELIMITER ;

PostgreSQL:

sqlCopyEditCREATE OR REPLACE PROCEDURE GetEmployeeById(IN emp_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT * FROM employees WHERE id = emp_id;
END;
$$;

6. Executing a Stored Procedure

MySQL:

sqlCopyEditCALL GetEmployeeById(3);

PostgreSQL:

sqlCopyEditCALL GetEmployeeById(3);

7. Parameters in Stored Procedures (IN, OUT, INOUT)

• IN: Input only
• OUT: Output only
• INOUT: Both input and output

Example:

sqlCopyEditCREATE PROCEDURE UpdateSalary(IN emp_id INT, IN raise DECIMAL(10,2), OUT new_salary DECIMAL(10,2))
BEGIN
  UPDATE employees SET salary = salary + raise WHERE id = emp_id;
  SELECT salary INTO new_salary FROM employees WHERE id = emp_id;
END;

8. Control Flow in Stored Procedures

You can use IF, CASE, LOOP, WHILE, and REPEAT:

sqlCopyEditIF status = 'active' THEN
  UPDATE users SET last_login = NOW() WHERE id = user_id;
END IF;

Also includes exception handling via DECLARE ... HANDLER.

9. Creating and Using Functions

sqlCopyEditCREATE FUNCTION GetTotalOrders(cust_id INT)
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE total INT;
  SELECT COUNT(*) INTO total FROM orders WHERE customer_id = cust_id;
  RETURN total;
END;

10. Input Parameters in Functions

Functions can take multiple IN parameters:

sqlCopyEditCREATE FUNCTION AddNumbers(a INT, b INT)
RETURNS INT
RETURN a + b;

11. Return Statements and Data Types in Functions

• Use RETURNS type to define return type
• Must include a RETURN statement
• Only one value can be returned directly

sqlCopyEditRETURN CONCAT(first_name, ' ', last_name);

12. Using Stored Functions in SQL Queries

sqlCopyEditSELECT name, GetTotalOrders(id) AS total_orders
FROM customers;

Stored functions can be used in:

• SELECT
• WHERE
• ORDER BY
• GROUP BY

13. Real-World Use Case: Generating Reports

Procedure:

sqlCopyEditCREATE PROCEDURE MonthlySalesReport(IN report_month DATE)
BEGIN
  SELECT product_id, SUM(quantity), SUM(total)
  FROM sales
  WHERE MONTH(sale_date) = MONTH(report_month)
  GROUP BY product_id;
END;

Used for dashboards or automated report generation.

14. Real-World Use Case: Reusable Business Rules

Function:

sqlCopyEditCREATE FUNCTION IsEligibleForDiscount(purchase_total DECIMAL(10,2))
RETURNS BOOLEAN
RETURN purchase_total > 500;

Used inside SELECT, WHERE, or other procedures.

15. Error Handling in Stored Procedures

Use DECLARE HANDLER to catch and handle errors:

sqlCopyEditDECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  -- Log error or set default values
END;

This improves stability in mission-critical operations.

16. Security and Permissions

• Routines can be granted EXECUTE privileges separately
• Users can call a procedure without knowing the internal query
• You can restrict DML operations inside routines with role control

17. Performance Considerations

• Reduces round-trips between client and server
• Use compiled execution plans
• Avoid excessive nesting or recursion
• Reuse prepared logic for heavy tasks

18. Modifying and Dropping Stored Routines

Modify:

sqlCopyEditDROP PROCEDURE IF EXISTS UpdateSalary;
CREATE PROCEDURE UpdateSalary(...) ...

Drop:

sqlCopyEditDROP PROCEDURE procedure_name;
DROP FUNCTION function_name;

Use SHOW PROCEDURE STATUS or SHOW CREATE FUNCTION to inspect definitions.

19. Best Practices for Writing Stored Routines

• Use clear, consistent naming (sp_, fn_, etc.)
• Comment thoroughly for maintainability
• Keep logic modular and focused
• Handle exceptions explicitly
• Separate input validation from core logic

20. Summary and What’s Next

Stored procedures and functions are vital for modular, secure, and high-performance SQL development. They help standardize business logic, automate tasks, and improve performance through reusable logic blocks.