What are Stored Procedures?

Stored procedures are precompiled SQL statements stored within the database. They offer several advantages:

• Reduced Network Traffic: Only the procedure name and parameters are sent over the network.
• Improved Security: Data access can be controlled through the stored procedure, limiting direct table access.
• Code Reusability: Procedures can be called from multiple applications, promoting code consistency.
• Maintainability: Modifications to the logic only need to be done in one place (the stored procedure).

Control Flow Statements in Stored Procedures

Control flow statements allow you to control the order of execution of statements within a stored procedure. They are essential for implementing conditional logic and repetition. MySQL supports several control flow statements:

• IF: Conditional execution of statements based on a condition.
• CASE: Conditional execution based on multiple possible conditions.
• WHILE: Repeated execution of statements as long as a condition is true.
• REPEAT: Repeated execution of statements until a condition is true.
• LOOP: Unconditional looping (needs explicit exit).
• LEAVE: Exits a loop or stored procedure.
• ITERATE: Skips the remaining statements in the current loop iteration and continues with the next iteration.

Implementing Control Flow Logic

IF Statement

The IF statement allows you to execute a block of code only if a certain condition is true. You can also include ELSEIF and ELSE blocks for alternative execution paths.

 DELIMITER //
CREATE PROCEDURE CheckNumber(IN num INT)
BEGIN
  IF num > 0 THEN
    SELECT 'Positive Number';
  ELSEIF num < 0 THEN
    SELECT 'Negative Number';
  ELSE
    SELECT 'Zero';
  END IF;
END //
DELIMITER ;

CALL CheckNumber(10);  -- Output: Positive Number
CALL CheckNumber(-5);  -- Output: Negative Number
CALL CheckNumber(0);   -- Output: Zero 

CASE Statement

The CASE statement allows you to choose one of several blocks of code to execute, depending on the value of an expression or a set of conditions.

 DELIMITER //
CREATE PROCEDURE GetDayName(IN day_number INT)
BEGIN
  CASE day_number
    WHEN 1 THEN SELECT 'Monday';
    WHEN 2 THEN SELECT 'Tuesday';
    WHEN 3 THEN SELECT 'Wednesday';
    WHEN 4 THEN SELECT 'Thursday';
    WHEN 5 THEN SELECT 'Friday';
    WHEN 6 THEN SELECT 'Saturday';
    WHEN 7 THEN SELECT 'Sunday';
    ELSE SELECT 'Invalid Day Number';
  END CASE;
END //
DELIMITER ;

CALL GetDayName(3);   -- Output: Wednesday
CALL GetDayName(8);   -- Output: Invalid Day Number 

WHILE Loop

The WHILE loop repeatedly executes a block of code as long as a condition is true.

 DELIMITER //
CREATE PROCEDURE PrintNumbers(IN max_number INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= max_number DO
    SELECT i;
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

CALL PrintNumbers(5);  -- Output: 1, 2, 3, 4, 5 (as separate result sets) 

REPEAT Loop

The REPEAT loop repeatedly executes a block of code until a condition becomes true. It is similar to a WHILE loop, but the condition is checked *after* the loop body is executed at least once.

 DELIMITER //
CREATE PROCEDURE CalculateSum(IN max_number INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE sum INT DEFAULT 0;
  REPEAT
    SET sum = sum + i;
    SET i = i + 1;
  UNTIL i > max_number
  END REPEAT;
  SELECT sum;
END //
DELIMITER ;

CALL CalculateSum(5);  -- Output: 15 (1+2+3+4+5) 

LOOP, LEAVE, and ITERATE

The LOOP statement creates an unconditional loop. You must use LEAVE to exit the loop and ITERATE to skip the current iteration.

 DELIMITER //
CREATE PROCEDURE PrintEvenNumbers(IN max_number INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    my_loop: LOOP
        IF i > max_number THEN
            LEAVE my_loop;
        END IF;

        IF i MOD 2 != 0 THEN
            SET i = i + 1;
            ITERATE my_loop;
        END IF;

        SELECT i;
        SET i = i + 1;
    END LOOP my_loop;
END //
DELIMITER ;

CALL PrintEvenNumbers(10); -- Output: 2, 4, 6, 8, 10 (as separate result sets) 

Best Practices

• Keep Procedures Concise: Break down complex logic into smaller, more manageable procedures.
• Use Meaningful Names: Choose descriptive names for procedures and variables.
• Comment Your Code: Explain the purpose of each section of code.
• Handle Errors: Implement error handling to gracefully handle unexpected situations.
• Test Thoroughly: Test procedures with various inputs to ensure they function correctly.