Stored procedures are a powerful feature of SQL that allow you to encapsulate complex queries and operations into reusable code blocks. They help in improving performance, reducing redundancy, and ensuring consistency in your database operations. In this article, we will delve into the creation and use of stored procedures in SQL, providing multiple code examples along with their results.

What are Stored Procedures?

A stored procedure is a set of SQL statements that can be stored and executed on the database server. They are particularly useful for repetitive tasks, complex transactions, and encapsulating business logic.

Benefits of Using Stored Procedures

1. Performance Improvement: Stored procedures are precompiled and stored in the database, reducing the execution time.
2. Code Reusability: Once created, stored procedures can be reused in different parts of your application.
3. Security: They can help prevent SQL injection attacks by separating the SQL logic from the application code.
4. Maintainability: Changes to the logic can be made in one place without altering the application code.

Creating a Simple Stored Procedure

Let's start with a basic example: a stored procedure that returns a list of all employees.

DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT * FROM employees;
END //
DELIMITER ;

Result: Executing the stored procedure:

CALL GetAllEmployees();
+----+-------+----------+--------+------------+
| id | name  | position | salary | department |
+----+-------+----------+--------+------------+
| 1  | John  | Manager  | 90000  | HR         |
| 2  | Alice | Engineer | 85000  | IT         |
| 3  | Bob   | Clerk    | 40000  | Admin      |
+----+-------+----------+--------+------------+

Stored Procedure with Parameters

Now, let's create a stored procedure that accepts parameters. This procedure retrieves employee details based on the department.

DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(IN dept VARCHAR(50))
BEGIN
    SELECT * FROM employees WHERE department = dept;
END //
DELIMITER ;

Result: Executing the stored procedure:

CALL GetEmployeesByDept('IT');
+----+-------+----------+--------+------------+
| id | name  | position | salary | department |
+----+-------+----------+--------+------------+
| 2  | Alice | Engineer | 85000  | IT         |
+----+-------+----------+--------+------------+

Stored Procedure with Output Parameters

Stored procedures can also have output parameters to return values.

DELIMITER //
CREATE PROCEDURE GetEmployeeCountByDept(IN dept VARCHAR(50), OUT count INT)
BEGIN
    SELECT COUNT(*) INTO count FROM employees WHERE department = dept;
END //
DELIMITER ;

Result: Executing the stored procedure:

CALL GetEmployeeCountByDept('IT', @count);
SELECT @count AS EmployeeCount;
+---------------+
| EmployeeCount |
+---------------+
| 1             |
+---------------+

Handling Transactions in Stored Procedures

Stored procedures are ideal for handling complex transactions. Here's an example where we transfer funds between two accounts.

DELIMITER //
CREATE PROCEDURE TransferFunds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
        ROLLBACK;
    END;

START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
END //
DELIMITER ;

Result: Executing the stored procedure:

CALL TransferFunds(1, 2, 100.00);
Query OK, 1 row affected (0.04 sec)

Stored Procedure with Looping

You can also use loops within stored procedures. Here's an example that generates a multiplication table for a given number.

DELIMITER //
CREATE PROCEDURE MultiplicationTable(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    CREATE TEMPORARY TABLE temp_table (n INT, result INT);

WHILE i <= 10 DO
        INSERT INTO temp_table VALUES (i, i * num);
        SET i = i + 1;
    END WHILE;
    SELECT * FROM temp_table;
    DROP TEMPORARY TABLE temp_table;
END //
DELIMITER ;

Result: Executing the stored procedure:

CALL MultiplicationTable(5);
+----+--------+
| n  | result |
+----+--------+
| 1  | 5      |
| 2  | 10     |
| 3  | 15     |
| 4  | 20     |
| 5  | 25     |
| 6  | 30     |
| 7  | 35     |
| 8  | 40     |
| 9  | 45     |
| 10 | 50     |
+----+--------+

Stored Procedure for Conditional Logic

Stored procedures can also include conditional logic. Here's an example of a procedure that applies a bonus to employees based on their performance rating.

DELIMITER //
CREATE PROCEDURE ApplyBonus(IN rating INT)
BEGIN
    IF rating = 5 THEN
        UPDATE employees SET salary = salary * 1.10;
    ELSEIF rating = 4 THEN
        UPDATE employees SET salary = salary * 1.05;
    ELSE
        UPDATE employees SET salary = salary * 1.02;
    END IF;
END //
DELIMITER ;

Result: Executing the stored procedure:

CALL ApplyBonus(5);

+----+-------+----------+--------+------------+
| id | name  | position | salary | department |
+----+-------+----------+--------+------------+
| 1  | John  | Manager  | 99000  | HR         |
| 2  | Alice | Engineer | 93500  | IT         |
| 3  | Bob   | Clerk    | 44000  | Admin      |
+----+-------+----------+--------+------------+

Stored Procedure with Error Handling

Proper error handling is crucial in stored procedures. Here's an example that demonstrates error handling.

DELIMITER //
CREATE PROCEDURE SafeTransfer(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transaction Failed' AS message;
    END;

START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
    SELECT 'Transaction Successful' AS message;
END //
DELIMITER ;

Result: Executing the stored procedure:

CALL SafeTransfer(1, 2, 100.00);
+----------------------+
| message              |
+----------------------+
| Transaction Successful |
+----------------------+

Conclusion

Stored procedures are a fundamental tool in MySQL for encapsulating complex logic and improving the efficiency and maintainability of your SQL code. By mastering stored procedures, you can enhance your database operations and ensure that your applications run smoothly and securely.

This article has covered a range of examples from simple queries to complex transactions and error handling. By practicing these examples, you will gain a deeper understanding of how to utilize stored procedures effectively in your own projects.

SQL Fundamentals

Thank you for your time and interest! 🚀 You can find even more content at SQL Fundamentals 💫