Stored Procedures and Functions

# CHAPTER 24

Stored Procedures and Functions #

1. Introduction #

2. Learning Objectives #

• Understand the architectural purpose of Stored Procedures.

• Create a Stored Procedure using parameters (IN, OUT).

• Execute a saved procedure using the CALL command.

• Differentiate between a Stored Procedure and a SQL Function.

• Understand the performance benefits of encapsulated logic.

3. What is a Stored Procedure? #

4. Creating a Stored Procedure #

Let's create a procedure that automatically transfers an employee to a new department and logs the transfer date.

-- Change the delimiter so MySQL doesn't get confused by the internal semicolons
DELIMITER //

CREATE PROCEDURE TransferEmployee(
    IN emp_id INT, 
    IN new_dept_id INT
)
BEGIN
    -- Step 1: Update the employee table
    UPDATE employees 
    SET dept_id = new_dept_id 
    WHERE id = emp_id;
    
    -- Step 2: Insert a record into the audit log!
    INSERT INTO transfer_logs (employee_id, old_dept, new_dept, transfer_date)
    VALUES (emp_id, 'Unknown', new_dept_id, NOW());
END //

-- Reset the delimiter
DELIMITER ;

5. Executing a Stored Procedure (CALL) #

-- Transfer Employee #105 to Department #3 instantly!
CALL TransferEmployee(105, 3);

*(Result: The database executes both the UPDATE and the INSERT queries internally in a fraction of a millisecond!)*

6. What is a SQL Function? #

Let's create a Function that calculates a 15% discount on a given price.

DELIMITER //

CREATE FUNCTION CalculateDiscount(original_price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE discounted_price DECIMAL(10,2);
    SET discounted_price = original_price * 0.85;
    
    -- A function MUST return a value!
    RETURN discounted_price; 
END //

DELIMITER ;

7. Using the Custom Function #

SELECT 
    product_name, 
    price AS 'Original Price',
    CalculateDiscount(price) AS 'Black Friday Price'
FROM products;

8. Mini Project: E-Commerce Security #

9. Common Mistakes #

• Syntax Variations: If you copy and paste a Stored Procedure tutorial written for Microsoft SQL Server (T-SQL) into a PostgreSQL database (PL/pgSQL), it will immediately crash. Procedural languages are highly specific to the database engine.

• The Delimiter Error: When writing procedures in MySQL Workbench or CLI, if you forget to change the DELIMITER //, the engine will execute the very first semicolon ; inside the BEGIN block, shattering the procedure creation.

10. Best Practices #

• Where does Business Logic Belong? The industry is split. Some architects put all logic in Stored Procedures (making the database incredibly powerful but hard to migrate). Modern web architects often prefer putting logic in the Node.js/PHP backend (making the code easier to version control with Git) and using the database purely for dumb storage. Balance is key.

11. Exercises #

1. 1. What command is used to execute a saved Stored Procedure?

1. 2. What is the fundamental difference between a Procedure and a Function regarding the RETURN keyword?

12. SQL Challenges #

CALL ArchiveOldUsers(2020);

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the concept of "Network Latency Optimization" regarding Stored Procedures. Why is it faster for a Node.js server to execute CALL ProcessPayroll(); rather than sending 500 individual UPDATE statements over the TCP/IP network?

• Q: Debate the modern architectural dilemma: Should complex business logic reside in Database Stored Procedures, or in the application backend (e.g., Python/Java)? What are the pros and cons of tying your logic tightly to the database engine?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #