Day 41: MySQL Stored Procedures - Writing Reusable SQL Code!

Let's explore Stored Procedures in MySQL, a powerful feature that allows us to write reusable and efficient SQL code!

? What is a Stored Procedure?

A?Stored Procedure?is a?predefined SQL script?stored in the database that can be executed?multiple times?with a single call. It helps reduce?redundancy, improve performance, and enhance security.

1. Creating a Basic Stored Procedure

? Example: Retrieve All Customers

DELIMITER //
CREATE PROCEDURE GetAllCustomers()
BEGIN
    SELECT * FROM customers;
END //
DELIMITER ;        

(Executes a SELECT query every time the procedure is called)

? How to Call the Stored Procedure?

CALL GetAllCustomers();        

2. Stored Procedure with Input Parameters

? Example: Get Orders by Customer ID

DELIMITER //
CREATE PROCEDURE GetOrdersByCustomer (IN cust_id INT)
BEGIN
    SELECT * FROM orders WHERE customer_id = cust_id; 
END //
DELIMITER ;        

? Call with a Specific Customer ID

CALL GetOrdersByCustomer(101)        

3. Stored Procedure with Output Parameters

? Example: Get Total Orders for a Customer

DELIMITER // 
CREATE PROCEDURE GetTotalOrders(IN cust_id INT, OUT total_orders INT)
    BEGIN 
        SELECT COUNT(*) INTO total_orders FROM orders WHERE customer_id =cust_id;
    END  //
DELIMITER ;        

? Calling the Procedure and Getting the Result

CALL GetTotalOrders(101, @total);
SELECT @total AS TotalOrders;        

(Returns total number of orders placed by Customer 101)

4. Stored Procedure with IF-ELSE Logic

? Example: Check Customer Status

DELIMITER //
CREATE PROCEDURE CheckCustomerStatus (IN cust_id INT, out status VARCHAR(20))
BEGIN
    DECLARE total_orders INT;
    SELECT COUNT(*) INTO total_orders FROM orders WHERE customer_id = cust_id;

    IF total_orders > 10 THEN
            SET status = 'Premium Customer';
    ELSE
             SET status = 'Regular Customer';
    END IF;    
END //
DELIMITER ;        

? Calling the Procedure

CALL CheckCustomerStatus(101, @status);
SELECT @status;        

(Returns Premium Customer or Regular Customer based on order count)

5. Deleting a Stored Procedure

? Removing an Unused Procedure

DROP PROCEDURE IF EXISTS GetAllCustomers;        

(Deletes the stored procedure if it exists)

Key Benefit of Stored Procedures

? Reusable - Write once, use multiple times

? Faster Execution - Reduces query parsing time

? Enhanced Security - Restricts direct access to tables

? Reduces Redundancy - Avoids repeating SQL code

#100DaysOfCode #MySQL #SQL #Database #StoredProcedures #Learning #BackendDevelopment