MySQL Stored Procedures

Stored Procedure Benefits

-- Compiled once, 
-- executed many times

-- Users call procedure,
-- not direct table access

-- Call from any application
CALL GetAllUsers();

-- Complex queries 
-- in simple procedure

🔹 Creating Stored Procedures

Create procedures using CREATE PROCEDURE statement. Use DELIMITER to change the statement delimiter temporarily since procedures contain semicolons. The BEGIN...END block contains the procedure body with SQL statements. Procedures can include variables, conditions, loops, and multiple queries.

-- Simple procedure without parameters
DELIMITER //
CREATE PROCEDURE GetUserCount()
BEGIN
    SELECT COUNT(*) AS total_users FROM users;
END //
DELIMITER ;

-- Procedure with IN parameter
DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

🔹 Calling Stored Procedures

Execute stored procedures using the CALL statement followed by the procedure name and any required parameters. Procedures can return result sets, modify data, or perform complex operations. You can call procedures from applications, scripts, or directly in MySQL client.

-- Call procedure without parameters
CALL GetAllUsers();

-- Call procedure with parameter
CALL GetUserById(5);

-- Call from application (example in PHP)
-- $result = $conn->query("CALL GetUserById(5)");

🔹 Parameters in Procedures

Stored procedures support three parameter types: IN for input values, OUT for output values, and INOUT for both. IN parameters pass values to the procedure. OUT parameters return values from the procedure. INOUT parameters can be modified within the procedure and returned.

-- IN parameter (input only)
DELIMITER //
CREATE PROCEDURE AddUser(IN username VARCHAR(50), IN email VARCHAR(100))
BEGIN
    INSERT INTO users (username, email) VALUES (username, email);
END //
DELIMITER ;

-- OUT parameter (output only)
DELIMITER //
CREATE PROCEDURE GetTotalUsers(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM users;
END //
DELIMITER ;

-- INOUT parameter (both input and output)
DELIMITER //
CREATE PROCEDURE DoubleValue(INOUT num INT)
BEGIN
    SET num = num * 2;
END //
DELIMITER ;

-- Call with OUT parameter
CALL GetTotalUsers(@total);
SELECT @total;  -- Returns: 150

🔹 Variables and Logic

Use DECLARE to create variables inside procedures. Implement conditional logic with IF statements and loops with WHILE or REPEAT. Variables store intermediate results and control flow. This allows complex business logic to be encapsulated within procedures for better organization and reusability.

-- Procedure with variables and IF statement
DELIMITER //
CREATE PROCEDURE CheckUserStatus(IN user_id INT)
BEGIN
    DECLARE user_count INT;
    DECLARE status_msg VARCHAR(100);
    
    SELECT COUNT(*) INTO user_count 
    FROM users WHERE id = user_id;
    
    IF user_count > 0 THEN
        SET status_msg = 'User exists';
    ELSE
        SET status_msg = 'User not found';
    END IF;
    
    SELECT status_msg AS message;
END //
DELIMITER ;

🔹 Viewing and Dropping Procedures

List all stored procedures using SHOW PROCEDURE STATUS or query information_schema. View procedure code with SHOW CREATE PROCEDURE. Drop procedures with DROP PROCEDURE when no longer needed. Use IF EXISTS to avoid errors when dropping non-existent procedures.

-- Show all procedures in database
SHOW PROCEDURE STATUS WHERE Db = 'your_database';

-- View procedure code
SHOW CREATE PROCEDURE GetAllUsers;

-- Drop a procedure
DROP PROCEDURE IF EXISTS GetAllUsers;

-- Drop multiple procedures
DROP PROCEDURE IF EXISTS proc1, proc2, proc3;

🔹 Best Practices

Use descriptive names for procedures and parameters. Keep procedures focused on single tasks. Document complex logic with comments. Handle errors appropriately using DECLARE handlers. Test procedures thoroughly before deployment. Avoid overly complex procedures that are hard to maintain and debug.